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]

 

[Aladin Akyurek]

Thanks, Aladin!

You are welcome.

Is there anyway of replacing SUMPRODUCT with SUMIFS as Sumproduct does slow the calculation?
Thanks again

=SUMPRODUCT(SUMIFS($F$6:$F$600,$B$6:$B$600,$B6,$C$6:$C$600,Y4:AC4)-SUMPRODUCT(~SUMIFS($F$6:$F$600,$B$6:$B$600,$B6,$C$6:$C$600,Y4:AC,$G$6:$G$600,{"Vacation";"Training";"Unavailable"})

would be probably a bit faster than

1. Just enter:

=SUMPRODUCT($F$6:$F$600,--($B$6:$B$600=$B6),--ISNUMBER(MATCH($C$6:$C$600,Y4:AC4,0)),1-ISNUMBER(MATCH($G$6:$G$600,{"Vacation","Training","Unavailable"},0)))

2. Control+shift+enter, not just enter:

=SUM(IF($B$6:$B$600=$B6,IF(ISNUMBER(MATCH($C$6:$C$600,Y4:AC4,0)),IF(1-ISNUMBER(MATCH($G$6:$G$600,{"Vacation","Training","Unavailable"},0)),$F$6:$F$600))))

 

[rafuk73]

You are welcome.



=SUMPRODUCT(SUMIFS($F$6:$F$600,$B$6:$B$600,$B6,$C$6:$C$600,Y4:AC4)-SUMPRODUCT(~SUMIFS($F$6:$F$600,$B$6:$B$600,$B6,$C$6:$C$600,Y4:AC,$G$6:$G$600,{"Vacation";"Training";"Unavailable"})

would be probably a bit faster than

1. Just enter:

=SUMPRODUCT($F$6:$F$600,--($B$6:$B$600=$B6),--ISNUMBER(MATCH($C$6:$C$600,Y4:AC4,0)),1-ISNUMBER(MATCH($G$6:$G$600,{"Vacation","Training","Unavailable"},0)))

2. Control+shift+enter, not just enter:

=SUM(IF($B$6:$B$600=$B6,IF(ISNUMBER(MATCH($C$6:$C$600,Y4:AC4,0)),IF(1-ISNUMBER(MATCH($G$6:$G$600,{"Vacation","Training","Unavailable"},0)),$F$6:$F$600))))

Many thanks, Aladin!

 

[Aladin Akyurek]

Many thanks, Aladin!

You are welcome. I suppose you caught that stupid ~ that MS Edge likes to add...

 

[barok]

<style> <!-- BODY,DIV,TABLE,THEAD,TBODY,TFOOT,TR,TH,TD,P { font-family:"Arial"; font-size:x-small } --> </style>

A width	B height	C weight		D result
250	250	50
220	220	20
300	150	30
220	200	50

<tbody>
[TD="align: center"]70[/TD]

</tbody>

i want to be result 120. but the criteria is width>=220, width <=250, height>=220, height <=250.
and i have formula =sumifs(C1:C5;A1:A5;"<=250";A1:A5;">=220";B1:B5;">=220";B1:B5;"<=250")

please help....

 

[Andrew Poulsom]

Welcome to MrExcel.

The value in B5 is less than 220 so C5 won't be included.

 

[barok]

Welcome to MrExcel.

The value in B5 is less than 220 so C5 won't be included.

if i want to get value 120 with the criteria it. how using formula is true ?

please help me....

 

[Andrew Poulsom]

Remove the references to column B.

 

[eotterholt]

Using SUMIFS requires re-arrangement...

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

Comma with countries, semi-colon with tags.

This is really close to what I'm looking for, question is how do I write a formula where I don't know the value of the criteria, I need it to search and find matches. So in this example, The value in "Alpha" "Bravo" are part of the download and can vary. So, I basically want to find first where if column A is the same value, then search column B for matches and then if A and B match, Sum C.

Does that make sense. If it's easier to use VBA, that works for me too, just don't know the what to write.

My question is on the forum here: http://www.mrexcel.com/forum/excel-questions/911472-copy-sepcific-rows-based-cell-value-two-cells.html

 

[barok]

Remove the references to column B.

ok. Thank you Mr. Andrew Poulsom.

 

[Aladin Akyurek]

This is really close to what I'm looking for, question is how do I write a formula where I don't know the value of the criteria, I need it to search and find matches. So in this example, The value in "Alpha" "Bravo" are part of the download and can vary. So, I basically want to find first where if column A is the same value, then search column B for matches and then if A and B match, Sum C.

Does that make sense. If it's easier to use VBA, that works for me too, just don't know the what to write.

My question is on the forum here: http://www.mrexcel.com/forum/excel-...sepcific-rows-based-cell-value-two-cells.html

ok. Thank you Mr. Andrew Poulsom.

If only a subset of column B is of interest, try to post a scaled-down sample using one of the methods listed in the following: Attachments or using this add-in:https://app.box.com/s/soezox25h3w0q5s4rcyl.