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!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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}
 
Upvote 0
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:
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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