Need array to sum based on multiple criteria

[Joshua H]

Hello,

[SEE REPLY POST BELOW FOR UPDATED FILE]

Basically I need an array that will sum up one column based on criteria from others.

Below is a snip of a super simplified version of what I've got:

Capture.PNG

Now if it loaded correctly there should be 4 columns. I need an array that will sum up the total items in store for the first instance of a store, But I want it summed by state. So without a formula, I want to sum total Items in store for NY. I only want the first instance of each store in NY which would be the sum of 220 and 75. This is where I want to end. Keep in mind this is overly simplified and my actual table is tens of thousands of rows.

I think I'm close with this formula but can't get it to work correctly.
{=IF(COUNTIFS(C:C,"NY",INDEX(A:A,MATCH(A:A,A:A,0)):A:A,A:A)=1,SUM(INDEX(D:D,MATCH(A:A,A:A,0)):A:A,A:A),"0")}


Any help would be greatly appreciated.

[AliGW]

Welcome! Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[mahju]

Hi
Your output is not so clear to me. Please upload an excel worksheet with input and expected output result

Any way you can use sumifs or sumproduct for more than one criteria. if first item is always eggs as in your data you can use

Syntax
sumifs(sum_range, criteria_rabge1, criteria1, criteria_rabge2, criteria2, .....

=sumifs(d2:d17,c2:c17,"NY",b2:b18,"eggs")

=sumproduct((c2:c17="NY")*(b2:b17="eggs")*(d2:d17))

Regards

[Joshua H]

Here is a more similar book to that which I am actually using.

You can see the ultimate goal is to get a table that will give me my store count by state as well as by size.
State can be found in column D but size is the 4th and 5th digit in column A.
I want to know how many stores are below 20 equal to 20 and above 20 for each state.
To get the numbers I have now I added the three columns highlighted in red.
However my ultimate goal would be to have one formula doing all the work instead of additional columns.

Attached is the excel file if you need any other information I'd be happy to provide.

if anyone can help it would mean alot

keep in mind I only want to add up the first instance of each section because in my full workbook each section can appear more than 100 times and I only want it counted once.