Trying to SUMIFS where criteria is an array

[Dogchow89]

I have a table that includes data such as Item Number, Branch Number, and other various sales information that looks like this:

Item Number	Branch #	On hand
100CN2H	1	50
100CN2H	5	500
50CNFH2Z	1	25
62C120THR2H <tbody> </tbody>	1 <tbody> </tbody>	1268
62C120THR2H	3	0
62C120THR2H	4	0
62C120THR2H	5	90

<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.

 

[FDibbins]

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 Number	Branch #	On hand			1358​	1268​	90​
2​	100CN2H	1	50		62C120THR2H	Company	North	South
3​	100CN2H	5	500		1​	1268​	1268​
4​	50CNFH2Z	1	25		2​	0​
5​	62C120THR2H	1	1268		3​	0​	0​
6​	62C120THR2H	3	0		4​	0​
7​	62C120THR2H	4	0		5​	90​		90​
8​	62C120THR2H	5	90

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]

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]

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!

 

[FDibbins]

wait, you want to do this for each 1 of 100+ items, too?