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]

 

[Andrew Poulsom]

That would be:

=SUMPRODUCT(((A2:A17<>"Europe")*(A2:A17<>"USA")),((B2:B17="Alpha")+(B2:B17="Bravo")),--(C2:C17="South"),D2:D17)

 

[aghlayeshkusa]

That would be:

=SUMPRODUCT(((A2:A17<>"Europe")*(A2:A17<>"USA")),((B2:B17="Alpha")+(B2:B17="Bravo")),--(C2:C17="South"),D2:D17)

Ah, of course, multiplication... I've just drawn a map of 1's and 0's to understand how this works, and now makes perfect sense!

Thank you very much for your help!

 

[mavicangeles]

Hi Sir,Seeking for help regarding this functionhere is my encoded filesDate Received "Date Assigned" Transaction Type Assigned To Cycle Time (Mins) Completion Date " Aging(No. of Days) "1-May 1-May EAM Code Creation VICKY 8 1-May - 1-May 1-May EAM Code Creation VICKY 8 1-May - 1-May 1-May EAM Code Creation VICKY 8 1-May - 1-May 1-May EAM Code Creation VICKY 8 1-May - 1-May 1-May EAM Code Creation VICKY 8 1-May - 1-May 1-May EAM Code Creation VICKY 8 1-May - 1-May 1-May EAM Code Creation VICKY 8 1-May - 1-May 1-May EAM Code Creation VICKY 8 1-May - 1-May 1-May EAM Code Creation VICKY 8 1-May - 1-May 1-May EAM Code Creation VICKY 8 1-May - 1-May 1-May EAM Code Creation VICKY 8 1-May - how can i compute the total cycle time of all completed adjustment?can i ask your e-mail address so that i can forward the whole file for your reference.Thanks in Advance,mavic

 

[Andrew Poulsom]

I can't decipher your data, sorry.

 

[mavicangeles]

can i send it to your email address?what email add?

 

[Oeldere]

And did you consider using a pivot table?

 

[Andrew Dustin Limento]

Dear all, need help, i know from previous discussion we cannot use formula such as =SUM(SUMIFS(E3:E10;B3:B10;B14;C3:C10;{"dog";"cat"};D3:D10;{"male";"female"})) and should replace with sumproduct formula, but what if i still need the first criteria B3:B10;B14 (as a primary key of my database), because using sumproduct we use no reference cell like in vlookup and sumifs.

Thank you for the help

 

[Aladin Akyurek]

Dear all, need help, i know from previous discussion we cannot use formula such as =SUM(SUMIFS(E3:E10;B3:B10;B14;C3:C10;{"dog";"cat"};D3:D10;{"male";"female"})) and should replace with sumproduct formula, but what if i still need the first criteria B3:B10;B14 (as a primary key of my database), because using sumproduct we use no reference cell like in vlookup and sumifs.

Thank you for the help

Try one of:

1)

=SUM(SUMIFS(E3:E10;B3:B10;B14;C3:C10;{"dog";"cat"};D3:D10;{"male","female"}))

2)

=SUMPRODUCT(E3:E10;--(B3:B10=B14);--ISNUMBER(MATCH(C3:C10;{"dog";"cat"};0));
     --ISNUMBER(MATCH(D3:D10;{"male";"female"},0)))

 

[Andrew Dustin Limento]

Try one of:

1)

=SUM(SUMIFS(E3:E10;B3:B10;B14;C3:C10;{"dog";"cat"};D3:D10;{"male","female"}))

2)

=SUMPRODUCT(E3:E10;--(B3:B10=B14);--ISNUMBER(MATCH(C3:C10;{"dog";"cat"};0));
     --ISNUMBER(MATCH(D3:D10;{"male";"female"},0)))

Thank you very much Aladin, yes, i try the first code and its working like charm, just need a little modification, instead of using comma "," i use "\", don't know why but when use "," an error message pop out. The second code looked a little complicated for me... haha, need to learn about -- and isnumber first. Thank you again!

 

[Aladin Akyurek]

Thank you very much Aladin, yes, i try the first code and its working like charm, just need a little modification, instead of using comma "," i use "\", don't know why but when use "," an error message pop out. The second code looked a little complicated for me... haha, need to learn about -- and isnumber first. Thank you again!

You are welcome.