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?

 

[miconian]

So what if I want to EXCLUDE the array?

This doesn't work:

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

Neither does this:

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

What am I missing?

 

[barry houdini]

The problem is that "not orange" includes red and vice versa - to exclude multiple values change to SUMPRODUCT, i.e.

=SUMPRODUCT(C2:C5,(A2:A5="CNN")*ISNA(MATCH(B2:B5,{"orange","red"},0)))

ISNA excludes, change to ISNUMBER to include.....

 

[Aladin Akyurek]

So what if I want to EXCLUDE the array?

This doesn't work:

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

Neither does this:

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

What am I missing?

Nothing missing. The function cannot process criteria specs like {"<>orange","<>red"} or "<>"&{"orange","red"}.

Better to switch to:

Either...

=SUMPRODUCT(C2:C5,--(A2:A5="CNN"),1-ISNUMBER(MATCH(B2:B5,{"orange","red"},0)))

Or, control+shift+enter, not just enter...

=SUM(IF(A2:A5="CNN",IF(1-ISNUMBER(MATCH(B2:B5,{"orange","red"},0)),C2:C5)))

 

[miconian]

Thanks very much, Aladin. It took me a few hours of research to understand what you're doing with those formulas, but I think I get it now.

 

[Aladin Akyurek]

Thanks very much, Aladin. It took me a few hours of research to understand what you're doing with those formulas, but I think I get it now.

That's great. Thanks for providing feedback.

 

[jgreigdavis]

Is it possible to pull the Array values from another cell? Replace the "Red","Orange" with a cell reference or vlookup value?

 

[Aladin Akyurek]

Is it possible to pull the Array values from another cell? Replace the "Red","Orange" with a cell reference or vlookup value?

Criteria:

Let X2 house CNN and Y2:Y3 orange and red...

=SUMPRODUCT(C2:C5,--(A2:A5="CNN"),1-ISNUMBER(MATCH(B2:B5,{"orange","red"},0)))

becomes:

=SUMPRODUCT(C2:C5,--(A2:A5=X2),1-ISNUMBER(MATCH(B2:B5,Y2:Y3,0)))

and

=SUM(IF(A2:A5="CNN",IF(1-ISNUMBER(MATCH(B2:B5,{"orange","red"},0)),C2:C5)))

becomes:

=SUM(IF(A2:A5=X2,IF(1-ISNUMBER(MATCH(B2:B5,Y2:Y3,0)),C2:C5)))

which needs control+shift+enter.

Is this what you intended to know?

By the way:

=SUMPRODUCT(SUMIFS(E2:E10,F2:F10=K2,G2:G10,L2:L3))

would be the form which does not involve negation.

 

[Jonmo1]

Perhaps

=SUMIFS(C2:C5,A2:A5,"CNN",B2:B5,"<>"&Y2,B2:B5,"<>"&Y3)


note, a simpler solution to the question in Post #11
=SUMIFS(C2:C5,A2:A5,"CNN",B2:B5,"<>orange",B2:B5,"<>red")

 

[jgreigdavis]

Criteria:

Let X2 house CNN and Y2:Y3 orange and red...

=SUM(IF(A2:A5=X2,IF(1-ISNUMBER(MATCH(B2:B5,Y2:Y3,0)),C2:C5)))

which needs control+shift+enter.

This is working, Thank you.

=SUMPRODUCT(SUMIFS(E2:E10,F2:F10=K2,G2:G10,L2:L3))

would be the form which does not involve negation.

This part I don't understand.

 

[Aladin Akyurek]

This is working, Thank you.

You are welcome.

This part I don't understand.

If you are on a post-2003 Excel system, you can also invoke SumIfs in combination with e.g. SumProduct in order to capture, as it were, the result wit Or in it...

In:

=SUMPRODUCT(SUMIFS(E2:E10,F2:F10=K2,G2:G10,L2:L3))

we have 1 criterion in K2 for F2:F10 and 2 criteria in L2:L3 which must hold for G2:G10.