Multiple Criteria Sumifs in Multiple Columns

C

chowyg

New Member
Joined
Jul 20, 2011
Messages
11
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]
 
Sort by date Sort by votes

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
chowyg said:
That doesn't work. When you use multiple arrays they work as a matching function.

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

=(Europe*Alpha*South)+(USA*Bravo)

I need a formula that will go.

=(Europe*Alpha*South)+(Europe*Bravo*South)+(USA*Alpha*South)+(USA*Bravo*South*)

please excuse the bold, it turned on and I can't turn it off.
Click to expand...

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

yields 240. Is that wrong?

[TABLE="width: 336"]
<colgroup><col style="width: 48pt;" span="7" width="64"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: white"]Region[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Team[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Sub Region[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Sales[/TD]
[TD="class: xl70, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl70, width: 64, bgcolor: transparent"] [/TD]
[TD="class: xl70, width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"]ASIA[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Alpha[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]North[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]30[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]240[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"]ASIA[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Bravo[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]South[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]40[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"]ASIA[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Alpha[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]South[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]110[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"]ASIA[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Bravo[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]North[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]120[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"]Brazil[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Alpha[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]North[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]70[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"]Brazil[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Bravo[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]South[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]80[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"]Brazil[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Alpha[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]South[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]150[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: white"]Brazil[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Bravo[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]North[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]160[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #538DD5"]Europe[/TD]
[TD="class: xl68, width: 64, bgcolor: #538DD5"]Alpha[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]North[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]10[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #538DD5"]Europe[/TD]
[TD="class: xl68, width: 64, bgcolor: #538DD5"]Bravo[/TD]
[TD="class: xl68, width: 64, bgcolor: #538DD5"]South[/TD]
[TD="class: xl69, width: 64, bgcolor: #538DD5"]20[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #538DD5"]Europe[/TD]
[TD="class: xl68, width: 64, bgcolor: #538DD5"]Alpha[/TD]
[TD="class: xl68, width: 64, bgcolor: #538DD5"]South[/TD]
[TD="class: xl69, width: 64, bgcolor: #538DD5"]90[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #538DD5"]Europe[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Charlie[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]North[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]100[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #538DD5"]USA[/TD]
[TD="class: xl68, width: 64, bgcolor: #538DD5"]Alpha[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]North[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]50[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #538DD5"]USA[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]Charlie[/TD]
[TD="class: xl68, width: 64, bgcolor: #538DD5"]South[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]60[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #538DD5"]USA[/TD]
[TD="class: xl68, width: 64, bgcolor: #538DD5"]Alpha[/TD]
[TD="class: xl68, width: 64, bgcolor: #538DD5"]South[/TD]
[TD="class: xl69, width: 64, bgcolor: #538DD5"]130[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 64, bgcolor: #538DD5"]USA[/TD]
[TD="class: xl68, width: 64, bgcolor: #538DD5"]Bravo[/TD]
[TD="class: xl65, width: 64, bgcolor: white"]North[/TD]
[TD="class: xl66, width: 64, bgcolor: white"]140[/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[TD="class: xl70, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
...Strange, It didn't work the first time, but it did the second time. What's the difference between "," and ";"? I hate to keep making this more difficult, but my data is a thousand times more complex than the examples I've been giving. Aladin, can you explain to me how that formula would work if there was a third sub region?

=SUM(SUMIFS(D:D,A:A,{"Europe";"USA";"Brazil"},B:B,{"Alpha","Bravo"},C:C,{"South";"East"}))


[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Region[/TD]
[TD="width: 64"]Team[/TD]
[TD="width: 64"]Sub Region[/TD]
[TD="width: 64"]Sales[/TD]
[/TR]
[TR]
[TD]ASIA[/TD]
[TD]Alpha[/TD]
[TD]North[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]ASIA[/TD]
[TD]Bravo[/TD]
[TD]South[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]ASIA[/TD]
[TD]Alpha[/TD]
[TD]South[/TD]
[TD="align: right"]110[/TD]
[/TR]
[TR]
[TD]ASIA[/TD]
[TD]Bravo[/TD]
[TD]North[/TD]
[TD="align: right"]120[/TD]
[/TR]
[TR]
[TD]Brazil[/TD]
[TD]Alpha[/TD]
[TD]South[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD]Brazil[/TD]
[TD]Charlie[/TD]
[TD]South[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]Brazil[/TD]
[TD]Charlie[/TD]
[TD]South[/TD]
[TD="align: right"]19[/TD]
[/TR]
[TR]
[TD]Brazil[/TD]
[TD]Bravo[/TD]
[TD]East[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD]Alpha[/TD]
[TD]North[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD]Bravo[/TD]
[TD]South[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD]Alpha[/TD]
[TD]South[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]Europe[/TD]
[TD]Charlie[/TD]
[TD]North[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Alpha[/TD]
[TD]North[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Charlie[/TD]
[TD]South[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Alpha[/TD]
[TD]South[/TD]
[TD="align: right"]130[/TD]
[/TR]
[TR]
[TD]USA[/TD]
[TD]Bravo[/TD]
[TD]North[/TD]
[TD="align: right"]140[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
chowyg said:
...Strange, It didn't work the first time, but it did the second time. What's the difference between "," and ";"? I hate to keep making this more difficult, but my data is a thousand times more complex than the examples I've been giving. Aladin, can you explain to me how that formula would work if there was a third sub region?

=SUM(SUMIFS(D:D,A:A,{"Europe";"USA";"Brazil"},B:B,{"Alpha","Bravo"},C:C,{"South";"East"}))

...
Click to expand...

That won't work... The rows by columns set up no longer holds here.

You need to switch to something like:
Rich (BB code): 
=SUMPRODUCT(
  D2:D17,
  --ISNUMBER(MATCH(A2:A17,{"Europe","USA","Brazil"},0)),
  --ISNUMBER(MATCH(B2:B17,{"Alpha","Bravo"},0)),
  --ISNUMBER(MATCH(C2:C17,{"South","North"},0)))
 
Upvote 0
Hi,

Is it possible to replace the ... {"Europe";"USA";"Brazil"}..notation with a notation that references a cell e.g {"$A$10";"$A$14";"$A$9l"}? I would like to have a section in the worksheet that has all possible regions. This is useful when additional regions are added. This could also be helpful as I can avoid changing the text in the formula, instead I could change entries in a reference cell.
 
Upvote 0
maryzu said:
Hi,

Is it possible to replace the ... {"Europe";"USA";"Brazil"}..notation with a notation that references a cell e.g {"$A$10";"$A$14";"$A$9l"}? I would like to have a section in the worksheet that has all possible regions. This is useful when additional regions are added. This could also be helpful as I can avoid changing the text in the formula, instead I could change entries in a reference cell.
Click to expand...

You can have Europe, USA, and Brazil in a range of its won, says, K2:K4. The formula then becomes:
Rich (BB code): 
=SUMPRODUCT(
  D2:D17,
  --ISNUMBER(MATCH(A2:A17,$K$2:$K$4,0)),
  --ISNUMBER(MATCH(B2:B17,{"Alpha","Bravo"},0)),
  --ISNUMBER(MATCH(C2:C17,{"South","North"},0)))
 
Upvote 0
Aladin Akyurek said:
You can have Europe, USA, and Brazil in a range of its won, says, K2:K4. The formula then becomes:
Rich (BB code): 
=SUMPRODUCT(
  D2:D17,
  --ISNUMBER(MATCH(A2:A17,$K$2:$K$4,0)),
  --ISNUMBER(MATCH(B2:B17,{"Alpha","Bravo"},0)),
  --ISNUMBER(MATCH(C2:C17,{"South","North"},0)))
Click to expand...

Thanks it works.
 
Upvote 0
Andrew Poulsom said:
Like this?

=SUMPRODUCT(((A2:A17="Europe")+(A2:A17="USA")),((B2:B17="Alpha")+(B2:B17="Bravo")),--(C2:C17="South"),D2:D17)
Click to expand...


Hello,

This works perfectly for what I need, except that I also need the "opposite" value, which means all continents except Europe and USA (the rest of the criteria is the same).

Can you please help me?

Thank you very much!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
SUMIF Multiple Columns based on criteria next to each cell
Replies
3
Views
552
Fluff
Fluff
excelbytes
  • Solved
SUMIFS With Multiple Criteria In One Column
2 3
Replies
26
Views
9K
KRice
K
JohnZ1156
  • Solved
SUMIFS with multiple criteria
2
Replies
17
Views
1K
KRice
K
E
  • Solved
Sumifs with multiple criteria
Replies
2
Views
346
ellison
E
I
  • Solved
Use another Cell as Sumifs OR criteria
Replies
4
Views
605
Fluff
Fluff

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,631
Latest member
a_potato

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top