Trying to SUMIFS where criteria is an array

Dogchow89

New Member
Joined
Jan 14, 2015
Messages
6
I have a table that includes data such as Item Number, Branch Number, and other various sales information that looks like this:

Item NumberBranch #On hand
100CN2H150
100CN2H5500
50CNFH2Z125
62C120THR2H

<tbody>
</tbody>
1

<tbody>
</tbody>
1268
62C120THR2H30
62C120THR2H40
62C120THR2H590

<tbody>
</tbody>

My report will go by part number and location, but here's what's tricky. The report needs to be by region, with multiple branches in a region. The "Region" array is dynamic according to which region the user chooses:

"A - Company-wide" = (1,3,5,6,7,8,9,11,66)
"B - North" = (1,3,"","",7,8,9,11,"")
"C - South" = ("","",5,6,"","","","",66)

So if I wanted to SUMIFS the On Hand of "62C120THR2H" for the South region, my ideal formula would look up the part number, and take all the entries where branch is in the south region (5, 6, or 66) and add them all up.

So taking the above table for example, my formula would be
=+SUMIFS(C1:C8,A1:A8,"62C120THR2H",B1:B8,Region)

But this gives me an #N/A error, which I'm assuming because the array sizes don't match? I have no idea where to go from here.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
I would probably create a small table with branches down the side and Regions across the top.

The table body would contain SUMIFS() which you would apply to each row as applicable...
A​
B​
C​
D​
E​
F​
G​
H​
1​
Item NumberBranch #On hand
1358​
1268​
90​
2​
100CN2H15062C120THR2HCompanyNorthSouth
3​
100CN2H5500
1​
1268​
1268​
4​
50CNFH2Z125
2​
0​
5​
62C120THR2H11268
3​
0​
0​
6​
62C120THR2H30
4​
0​
7​
62C120THR2H40
5​
90​
90​
8​
62C120THR2H590
E2 would be the Item Number you want (this could be a dropdown)
F3=SUMIFS($C:$C,$A:$A,$E$2,$B:$B,$E3)
copied down
Then copy that to the relevant rows for the different regions.

F1=SUM(F3:F7)
This would be the sum of each "region"
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
There are probably other ways of doing this, but if you have that many branches (or more) the necessary array formula could get too complex
 

Dogchow89

New Member
Joined
Jan 14, 2015
Messages
6
Well, the original data table in question is fairly big, and unfortunately I don't think I can break out another table like that, because the final report can contain 100+ items. That's why I was thinking of doing a SUMIFS per item...

Although if there is a better way to do it I'm definitely open to suggestions. Thanks!
 

Forum statistics

Threads
1,144,611
Messages
5,725,311
Members
422,610
Latest member
sanantonio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top