Sumifs with 'or' statement

nikegeo

Board Regular
Joined
Jul 23, 2010
Messages
52
Hey guys, ive been trying everything to nest an 'or' statement into a sumifs function with no success:
I have named ranges and the market is the range where i will want to sum if my criteria is one or the other(i.e. sum Billings values if the pitchstage is "pitching" and the market values are Australia or New Zealand
Im trying to add all pitching billings values for NZ and Aus... figured this would be the best way

=SUMIFS(Billings,Market,OR("Australia","New Zealand"),PitchStage,"Pitching")

Ive searched everywhere with no solution.

Thanks Guys!
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
Hey guys, ive been trying everything to nest an 'or' statement into a sumifs function with no success:
I have named ranges and the market is the range where i will want to sum if my criteria is one or the other(i.e. sum Billings values if the pitchstage is "pitching" and the market values are Australia or New Zealand
Im trying to add all pitching billings values for NZ and Aus... figured this would be the best way

=SUMIFS(Billings,Market,OR("Australia","New Zealand"),PitchStage,"Pitching")

Ive searched everywhere with no solution.

Thanks Guys!
Code:
=SUM(SUMIFS(Billings,Market,{"Australia","New Zealand"},PitchStage,"Pitching"))

Or...
Code:
=SUMPRODUCT(SUMIFS(Billings,Market,List,PitchStage,"Pitching"))
where List is a range which houses a set of criteria.
 

nikegeo

Board Regular
Joined
Jul 23, 2010
Messages
52
thanks for the quick response! The sum(sumifs... worked.

If you dont mind elaborating why you had to do the =sum(sumifs...
i see that theres an array for the markets...

sorry im a bit new to the more intermediate/advanced calculations

Thanks again!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
thanks for the quick response! The sum(sumifs... worked.

You are welcome.

If you dont mind elaborating why you had to do the =sum(sumifs...
i see that theres an array for the markets...
...

SumIf (or SumIfs) would create a result for each criteria specified in an array like in:

{0,7,14}

This array of results fed to the outer SUM yields a scalar (a total) value, that is, 21.
 

nikegeo

Board Regular
Joined
Jul 23, 2010
Messages
52

ADVERTISEMENT

got it. thanks again.
 

Houman

New Member
Joined
Jun 16, 2011
Messages
13
Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
I tried the:<o:p></o:p>
<o:p></o:p>
=SUM(SUMIFS(Billings,Market,{"Australia","New Zealand"},PitchStage,"Pitching"))<o:p></o:p>
<o:p></o:p>
formula and it works perfectly fine. However I tried to add an additional "or" term and I'm having problems.<o:p></o:p>
<o:p></o:p>
Here is the formula I'm using:<o:p></o:p>
<o:p></o:p>
=SUM(SUMIFS('Month Income'!$H:$H,'Month Income'!$A:$A,{"04","05","28","29"},'Month Income'!$D:$D,'Calc All'!$A3,'Month Income'!$I:$I,{"3","7","8"}))<o:p></o:p>
<o:p></o:p>
With only the {"3","7","8"} the result is correct, however with the {"04","05","28","29"} added in the resulting sum is at times greater and other smaller than the sum of the individual components. I have looked at the individual components and there is no way to arrive at the results from the above formula by adding and subtracting them together. The individual components added together give the correct result. i.e.:<o:p></o:p>
<o:p></o:p>
=SUM(SUMIFS('Month Income'!$H:$H,'Month Income'!$D:$D,'Calc All'!$A3,'Month Income'!$I:$I,{"3","7","8"},'Month Income'!$A:$A,”04”))<o:p></o:p>
+<o:p></o:p>
=SUM(SUMIFS('Month Income'!$H:$H,'Month Income'!$D:$D,'Calc All'!$A3,'Month Income'!$I:$I,{"3","7","8"},'Month Income'!$A:$A,”05”))<o:p></o:p>
+<o:p></o:p>
=SUM(SUMIFS('Month Income'!$H:$H,'Month Income'!$D:$D,'Calc All'!$A3,'Month Income'!$I:$I,{"3","7","8"},'Month Income'!$A:$A,”28”))<o:p></o:p>
+<o:p></o:p>
=SUM(SUMIFS('Month Income'!$H:$H,'Month Income'!$D:$D,'Calc All'!$A3,'Month Income'!$I:$I,{"3","7","8"},'Month Income'!$A:$A,”29”))<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Any help?<o:p></o:p>
<o:p></o:p>
Thank you,<o:p></o:p>
Houman
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203

ADVERTISEMENT

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
I tried the:<o:p></o:p>
<o:p></o:p>
=SUM(SUMIFS(Billings,Market,{"Australia","New Zealand"},PitchStage,"Pitching"))<o:p></o:p>
<o:p></o:p>
formula and it works perfectly fine. However I tried to add an additional "or" term and I'm having problems.<o:p></o:p>
<o:p></o:p>
Here is the formula I'm using:<o:p></o:p>
<o:p></o:p>
=SUM(SUMIFS('Month Income'!$H:$H,'Month Income'!$A:$A,{"04","05","28","29"},'Month Income'!$D:$D,'Calc All'!$A3,'Month Income'!$I:$I,{"3","7","8"}))<o:p></o:p>
<o:p></o:p>
With only the {"3","7","8"} the result is correct, however with the {"04","05","28","29"} added in the resulting sum is at times greater and other smaller than the sum of the individual components. I have looked at the individual components and there is no way to arrive at the results from the above formula by adding and subtracting them together. The individual components added together give the correct result. i.e.:<o:p></o:p>
<o:p></o:p>
=SUM(SUMIFS('Month Income'!$H:$H,'Month Income'!$D:$D,'Calc All'!$A3,'Month Income'!$I:$I,{"3","7","8"},'Month Income'!$A:$A,”04”))<o:p></o:p>
+<o:p></o:p>
=SUM(SUMIFS('Month Income'!$H:$H,'Month Income'!$D:$D,'Calc All'!$A3,'Month Income'!$I:$I,{"3","7","8"},'Month Income'!$A:$A,”05”))<o:p></o:p>
+<o:p></o:p>
=SUM(SUMIFS('Month Income'!$H:$H,'Month Income'!$D:$D,'Calc All'!$A3,'Month Income'!$I:$I,{"3","7","8"},'Month Income'!$A:$A,”28”))<o:p></o:p>
+<o:p></o:p>
=SUM(SUMIFS('Month Income'!$H:$H,'Month Income'!$D:$D,'Calc All'!$A3,'Month Income'!$I:$I,{"3","7","8"},'Month Income'!$A:$A,”29”))<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Any help?<o:p></o:p>
<o:p></o:p>
Thank you,<o:p></o:p>
Houman
i guess 'Month Income'!$A:$A is numeric. If that's the case, try to use...

'Month Income'!$A:$A,{4,5,28,29}

If 'Month Income'!$I:$I is also numeric,try to use...

'Month Income'!$I:$I,{3,7,8}
 

Houman

New Member
Joined
Jun 16, 2011
Messages
13
Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Thank you for the reply. The:<o:p></o:p>
<o:p></o:p>
'Month Income'!$A:$A<o:p></o:p>

is not numeric, I have removed the alpha info to make it shorter.<o:p></o:p>

<o:p></o:p>
'Month Income'!$A:$A,{"ABC04","ABC05","ABC28","ABC29"}<o:p></o:p>
<o:p></o:p>
is closer to the actual thing.<o:p></o:p>
<o:p></o:p>
However<o:p></o:p>
<o:p></o:p>
'Month Income'!$I:$I<o:p></o:p>
<o:p></o:p>
is numeric so I tried<o:p></o:p>
<o:p></o:p>
'Month Income'!$I:$I,{3,7,8} <o:p></o:p>
<o:p></o:p>
and got the same erroneous result.<o:p></o:p>
<o:p></o:p>
I'll appreciate further input.<o:p></o:p>
<o:p></o:p>
Thanks,<o:p></o:p>
Houman<o:p></o:p>
<o:p> </o:p>
P.S. Just to be sure that the logic is correct, I want to sum 'Month Income'!$H:$H, if 'Month Income'!$I:$I is any of ,{3,7,8} AND 'Month Income'!$A:$A is any of {"ABC04","ABC05","ABC28","ABC29"}.
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Thank you for the reply. The:<o:p></o:p>
<o:p></o:p>
'Month Income'!$A:$A<o:p></o:p>

is not numeric, I have removed the alpha info to make it shorter.<o:p></o:p>
<o:p></o:p>
'Month Income'!$A:$A,{"ABC04","ABC05","ABC28","ABC29"}<o:p></o:p>
<o:p></o:p>
is closer to the actual thing.<o:p></o:p>
<o:p></o:p>
However<o:p></o:p>
<o:p></o:p>
'Month Income'!$I:$I<o:p></o:p>
<o:p></o:p>
is numeric so I tried<o:p></o:p>
<o:p></o:p>
'Month Income'!$I:$I,{3,7,8} <o:p></o:p>
<o:p></o:p>
and got the same erroneous result.<o:p></o:p>
<o:p></o:p>
I'll appreciate further input.<o:p></o:p>
<o:p></o:p>
Thanks,<o:p></o:p>
Houman<o:p></o:p>
<o:p></o:p>
P.S. Just to be sure that the logic is correct, I want to sum 'Month Income'!$H:$H, if 'Month Income'!$I:$I is any of ,{3,7,8} AND 'Month Income'!$A:$A is any of {"ABC04","ABC05","ABC28","ABC29"}.

What does this...

=SUM(SUMIFS('Month Income'!$H:$H,'Month Income'!$A:$A,{"ABC04","ABC05","ABC28","ABC29"},'Month Income'!$D:$D,'Calc All'!$A3,'Month Income'!$I:$I,{3;7;8}))

return?
<o:p></o:p>
 

Houman

New Member
Joined
Jun 16, 2011
Messages
13
That does the trick! Many thanks. Would you be kind enough to explain the use of ";" vs. "," and why just on the numeric part? When there is only the numeric (i.e. {3,7,8}) the "," works fine but mixed with the {"ABC04","ABC05","ABC28","ABC29"} you need the ";". In any case I'm very grateful for your feedback.

Regards,
Houman
 

Watch MrExcel Video

Forum statistics

Threads
1,127,167
Messages
5,623,123
Members
415,956
Latest member
Footballtend

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
Top