# Sumifs with 'or' statement

#### nikegeo

##### Board Regular
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

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.

##### MrExcel MVP
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
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!

##### MrExcel MVP
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

got it. thanks again.

#### Houman

##### New Member
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

##### MrExcel MVP

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

##### New Member
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"}.

Last edited:

##### MrExcel MVP
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

##### New Member
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

Replies
5
Views
633
Replies
7
Views
332
Replies
1
Views
340
Replies
1
Views
252
Replies
1
Views
268

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.

### Which adblocker are you using?

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

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