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]

That does the trick! Many thanks.

You are welcome. Thanks for providing feedback.

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

We need to AND the items of two arrays, say X and Y, that is, every item from X must be correlated with each item of Y. That means we need an arrangement that allows for that: Horizontal (columnwise) Vs Vertical (rowwise) or vice versa. See an earlier thread on this issue:

http://www.mrexcel.com/forum/showthread.php?t=552814

 

[Houman]

Thank you for the extra information + the right on and timely solution.

 

[Aladin Akyurek]

Thank you for the extra information + the right on and timely solution.

You are welcome.

 

[JOR]

Hi,

I'm trying to do the same thing, but instead of using 'absolute' criteria, I want it to refer to the data in another cell...

i.e.
=SUMIFS(Data!S:S, Data!Q:Q, {Q14, Q15, Q16})

(The cells Q14, Q15, & Q16 are dates, and they change according to the reporting period)

thanks in advance

 

[Aladin Akyurek]

Hi,

I'm trying to do the same thing, but instead of using 'absolute' criteria, I want it to refer to the data in another cell...

i.e.
=SUMIFS(Data!S:S, Data!Q:Q, {Q14, Q15, Q16})

(The cells Q14, Q15, & Q16 are dates, and they change according to the reporting period)

thanks in advance

Try...

=SUMPRODUCT(SUMIFS(Data!S:S, Data!Q:Q, Q14:Q16))

 

[avi2009]

Thanks for all the helpful posts!

Can I take it one step further? For me it works fine when I have two categories with multiple variables I need to pull and I use a ',' in one and a ';' in the other.

The problem arises because I have a wider IF function, so I actually have four different criteria with multiple varibles to be pulled. (They are all text columns). Can anyone shed light on the order of magnitude of separators.

 

[Aladin Akyurek]

Thanks for all the helpful posts!

Can I take it one step further? For me it works fine when I have two categories with multiple variables I need to pull and I use a ',' in one and a ';' in the other.

The problem arises because I have a wider IF function, so I actually have four different criteria with multiple varibles to be pulled. (They are all text columns). Can anyone shed light on the order of magnitude of separators.

Could you elaborate a bit, even post the formula you have tried to implement?

 

[avi2009]

Sure, yes posting might be helpful

=IF('Welcome Page'!E5="Impact",SUM(SUMIFS('Data WCBI'!$X:$X,'Data WCBI'!$D:$D,MainPage!H1,'Data WCBI'!$AM:$AM,"2012",'Data WCBI'!$AY:$AY,{"Option1","Option2","Option3","Option4"},'Data WCBI'!$BL:$BL,{"Stage1";"Stage2"})),SUM(SUMIFS('Data WCBI'!$BK:$BK,'Data WCBI'!$D:$D,MainPage!H1,'Data WCBI'!$AM:$AM,"2012",'Data WCBI'!$AY:$AY,{"Option1","Option2","Option3","Option4"},'Data WCBI'!$BL:$BL,{"Stage1";"Stage2"})))

E5 on welcome page can be one of 3 options. The value range to sum is all in number format (ie X and BK)

Individually, the two parts of the if function work, but within the IF function it just brings back a random value.

Thanks for the help!

 

[Aladin Akyurek]

Sure, yes posting might be helpful

=IF('Welcome Page'!E5="Impact",SUM(SUMIFS('Data WCBI'!$X:$X,'Data WCBI'!$D:$D,MainPage!H1,'Data WCBI'!$AM:$AM,"2012",'Data WCBI'!$AY:$AY,{"Option1","Option2","Option3","Option4"},'Data WCBI'!$BL:$BL,{"Stage1";"Stage2"})),SUM(SUMIFS('Data WCBI'!$BK:$BK,'Data WCBI'!$D:$D,MainPage!H1,'Data WCBI'!$AM:$AM,"2012",'Data WCBI'!$AY:$AY,{"Option1","Option2","Option3","Option4"},'Data WCBI'!$BL:$BL,{"Stage1";"Stage2"})))

E5 on welcome page can be one of 3 options. The value range to sum is all in number format (ie X and BK)

Individually, the two parts of the if function work, but within the IF function it just brings back a random value.

Thanks for the help!

The formula works for me, assuming that:

'Data WCBI'!$AM:$AM consists of values like 2011, 2012, etc., not date values like 12-Jan-2012, etc.