Sumifs with 'or' statement

[nikegeo]

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!

 

[Aladin Akyurek]

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!

=SUM(SUMIFS(Billings,Market,{"Australia","New Zealand"},PitchStage,"Pitching"))

Or...

=SUMPRODUCT(SUMIFS(Billings,Market,List,PitchStage,"Pitching"))

where List is a range which houses a set of criteria.

 

[nikegeo]

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]

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]

got it. thanks again.

 

[Houman]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I tried the:<o></o>
<o></o>
=SUM(SUMIFS(Billings,Market,{"Australia","New Zealand"},PitchStage,"Pitching"))<o></o>
<o></o>
formula and it works perfectly fine. However I tried to add an additional "or" term and I'm having problems.<o></o>
<o></o>
Here is the formula I'm using:<o></o>
<o></o>
=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></o>
<o></o>
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></o>
<o></o>
=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></o>
+<o></o>
=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></o>
+<o></o>
=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></o>
+<o></o>
=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></o>
<o></o>
<o></o>
Any help?<o></o>
<o></o>
Thank you,<o></o>
Houman

 

[Aladin Akyurek]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I tried the:<o></o>
<o></o>
=SUM(SUMIFS(Billings,Market,{"Australia","New Zealand"},PitchStage,"Pitching"))<o></o>
<o></o>
formula and it works perfectly fine. However I tried to add an additional "or" term and I'm having problems.<o></o>
<o></o>
Here is the formula I'm using:<o></o>
<o></o>
=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></o>
<o></o>
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></o>
<o></o>
=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></o>
+<o></o>
=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></o>
+<o></o>
=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></o>
+<o></o>
=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></o>
<o></o>
<o></o>
Any help?<o></o>
<o></o>
Thank you,<o></o>
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]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thank you for the reply. The:<o></o>
<o></o>
'Month Income'!$A:$A<o></o>

is not numeric, I have removed the alpha info to make it shorter.<o></o>
<o></o>
'Month Income'!$A:$A,{"ABC04","ABC05","ABC28","ABC29"}<o></o>
<o></o>
is closer to the actual thing.<o></o>
<o></o>
However<o></o>
<o></o>
'Month Income'!$I:$I<o></o>
<o></o>
is numeric so I tried<o></o>
<o></o>
'Month Income'!$I:$I,{3,7,8} <o></o>
<o></o>
and got the same erroneous result.<o></o>
<o></o>
I'll appreciate further input.<o></o>
<o></o>
Thanks,<o></o>
Houman<o></o>
<o> </o>
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"}.

 

[Aladin Akyurek]

Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thank you for the reply. The:<o></o>
<o></o>
'Month Income'!$A:$A<o></o>

is not numeric, I have removed the alpha info to make it shorter.<o></o>
<o></o>
'Month Income'!$A:$A,{"ABC04","ABC05","ABC28","ABC29"}<o></o>
<o></o>
is closer to the actual thing.<o></o>
<o></o>
However<o></o>
<o></o>
'Month Income'!$I:$I<o></o>
<o></o>
is numeric so I tried<o></o>
<o></o>
'Month Income'!$I:$I,{3,7,8} <o></o>
<o></o>
and got the same erroneous result.<o></o>
<o></o>
I'll appreciate further input.<o></o>
<o></o>
Thanks,<o></o>
Houman<o></o>
<o></o>
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></o>

 

[Houman]

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