I have a table that includes data such as Item Number, Branch Number, and other various sales information that looks like this:
<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
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.
Item Number | Branch # | On hand | ||
100CN2H | 1 | 50 | ||
100CN2H | 5 | 500 | ||
50CNFH2Z | 1 | 25 | ||
<tbody> </tbody> |
<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.