Multiple Criteria Sumifs in Multiple Columns

[chowyg]

My table is below. I want to basically do a sum if with multiple criteria, but multiple criteria in multiple columns. I know how to do multiple criteria in a single column which is done like this "=SUM(SUMIFS(C2:C17,A2:A17,{"Europe","USA"},B2:B17,"Alpha"))"... but I'm trying to take it one step further and add in "Bravo" as well.

If this were a math function I want to do basically this. (X+Y)*(A+B), which is four functions. X*A, X*B, Y*A and Y*B. I need a formula that can sum the results using the multiple criteria. (Europe+USA)*(Alpha+Bravo).

Sum the criteria (Europe*Alpha, Europe* Bravo, USA*Alpha, USA*Bravo).

I been working on this for some time, it's quite stressful. I know the geniuses on here can help me resolve this. If this is confusing, please let me know and I will try to simply my needs better. Thanks in advance!



[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Region[/TD]
[TD="width: 64"]Team[/TD]
[TD="width: 64"]Sales[/TD]
[/TR]
[TR]
[TD]ASIA[/TD]
[TD]Alpha[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]ASIA[/TD]
[TD]Bravo[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]ASIA[/TD]
[TD]Alpha[/TD]
[TD="align: right"]110[/TD]
[/TR]
[TR]
[TD]ASIA[/TD]
[TD]Bravo[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Brazil[/TD]
[TD]Alpha[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]Brazil[/TD]
[TD]Bravo[/TD]
[TD="align: right"]80[/TD]
[/TR]
[TR]
[TD]Brazil[/TD]
[TD]Alpha[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD]Brazil[/TD]
[TD]Bravo[/TD]
[TD="align: right"]160[/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD]Alpha[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD]Bravo[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD]Alpha[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD]Bravo[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Alpha[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Bravo[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Alpha[/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Bravo[/TD]
[TD="align: right"]140[/TD]
[/TR]
</tbody>[/TABLE]

 

[Matini]

Using SUMIFS requires re-arrangement...

=SUM(SUMIFS(C2:C17,A2:A17,{"Europe","USA"},B2:B17,{"Alpha";"Bravo"}))

Comma with countries, semi-colon with tags.

Thank you so much. I have been looking for this for some time now