SUMIFS using an OR operator

[miconian]

Basically, I'm using SUMIFS, and the final criterion could go one of two ways: either EMER or APPC are valid values.

Here's my formula:

=SUMIFS(Data2011[Net Amount],Data2011[Internet Site],A13,Data2011[Confidence %],"B",Data2011[Ad Unit],"EMER")

The last criterion, Data2011[Ad Unit],"EMER" is correct, but incomplete. Another valid criteria for [Ad Unit] should be "APPC".

So I'm trying to do something like this:

=SUMIFS(Data2011[Net Amount],Data2011[Internet Site],A13,Data2011[Confidence %],"B",Data2011[Ad Unit],"EMER" OR Data2011[Ad Unit],"APPC")

or perhaps:

SUMIFS(Data2011[Net Amount],Data2011[Internet Site],A13,Data2011[Confidence %],"B",OR((Data2011[Ad Unit],"EMER"),(Data2011[Ad Unit],"APPC"))

What am I missing?

 

[MrKowz]

Try adding two separate SUMIFS statements together:


=SUMIFS(Data2011[Net Amount],Data2011[Internet Site],A13,Data2011[Confidence %],"B",Data2011[Ad Unit],"EMER")+
SUMIFS(Data2011[Net Amount],Data2011[Internet Site],A13,Data2011[Confidence %],"B",Data2011[Ad Unit],"APPC")

 

[Andrew Poulsom]

Try:

=SUM(SUMIFS(Data2011[Net Amount],Data2011[Internet Site],A13,Data2011[Confidence %],"B",Data2011[Ad Unit],{"EMER","APPC"}))

 

[miconian]

Excellent. In-formula array creation. Thank you!

 

[miconian]

Except, Andrew, it doesn't work. It only picks up on the first item in the array. Took me a while to realize why it wasn't working in some cases; those were the cases where the data for Ad Unit was the second array item. When I switched it to the first, the formula worked.

 

[Andrew Poulsom]

Can you post some sample data where it doesn't work please?

 

[miconian]

Okay. Suppose you enter the following data in the fifteen upper-left-most cells on a worksheet, starting with "site" in cell A1:

site     product     money
CNN     red          10
ABC     blue         15
CBS     green        25
NBC     yellow       67

And then, on the same worksheet, enter these two formulas in any cells:

=SUMIFS(C2:C5,A2:A5,"CNN",B2:B5,{"red","orange"})
=SUMIFS(C2:C5,A2:A5,"CNN",B2:B5,{"orange","red"})

The first formula will return 10. The second will return 0.

 

[Andrew Poulsom]

The equivalent of my formula would be:

=SUM(SUMIFS(C2:C5,A2:A5,"CNN",B2:B5,{"red","orange"}))
=SUM(SUMIFS(C2:C5,A2:A5,"CNN",B2:B5,{"orange","red"}))

both of which return 10.

 

[miconian]

Oh. Yes, I had missed that part.

Could you please explain how it works? What does it mean to sum a sumifs?

 

[Andrew Poulsom]

It sums the array returned by sumif.