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?

 

[jgreigdavis]

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

You just simplified my life!

Thanks again.

 

[Aladin Akyurek]

You just simplified my life!

Thanks again.

You are welcome. Thanks for providing feedback.

 

[WouterP]

What if I wanted to combine two OR-statements in one AND-statement? For example, everything where the color is in {"red", "blue", "green"} and site is in {"CNN", "NBC"}.

Considering this dataset, I would thus expect a result of 25.

[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"]site[/TD]
[TD="width: 64"]product[/TD]
[TD="width: 64"]money[/TD]
[/TR]
[TR]
[TD]CNN[/TD]
[TD]red[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]CNN[/TD]
[TD]blue[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]CBS[/TD]
[TD]green[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]NBC[/TD]
[TD]yellow[/TD]
[TD="align: right"]67[/TD]
[/TR]
</tbody>[/TABLE]

A solution for either a SUM (=25) or a COUNT (resulting in =2) would help me.

 

[Aladin Akyurek]

What if I wanted to combine two OR-statements in one AND-statement? For example, everything where the color is in {"red", "blue", "green"} and site is in {"CNN", "NBC"}.

Considering this dataset, I would thus expect a result of 25.

[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"]site[/TD]
[TD="width: 64"]product[/TD]
[TD="width: 64"]money[/TD]
[/TR]
[TR]
[TD]CNN[/TD]
[TD]red[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]CNN[/TD]
[TD]blue[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]CBS[/TD]
[TD]green[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD]NBC[/TD]
[TD]yellow[/TD]
[TD="align: right"]67[/TD]
[/TR]
</tbody>[/TABLE]

A solution for either a SUM (=25) or a COUNT (resulting in =2) would help me.

1.

=SUM(COUNTIFS(C2:C5,A2:A5,{"CNN";"NBC"},B2:B5,{"red","blue","green"}))

2.

=SUM(SUMIFS(C2:C5,A2:A5,{"CNN";"NBC"},B2:B5,{"red","blue","green"}))

The foregoing cannot be done ad infinitum.

A more general approach:

3.

=SUMPRODUCT(--ISNUMBER(MATCH(A2:A5,{"CNN","NBC"},0)),
  --ISNUMBER(MATCH(B2:B5,{"red","blue","green"},0)))

4.

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

 

[WouterP]

I deleted the C2:C5 you left in at the first code.
Works like a charm, thanks a million!

You prevented me from combining a LOT of countifs :')

 

[Aladin Akyurek]

I deleted the C2:C5 you left in at the first code.
Works like a charm, thanks a million!

You prevented me from combining a LOT of countifs :')

You are welcome.

 

[WouterP]

Things are getting complicated here. In another sum I'm trying to calculate I should only take dates that are in a certain range.

For example, this data range:
Higher than date(year(today()),month(today())-11),1)
AND
Lower than date(year(today()),month(today())-4),1)

Given the dataset:

site	product	money	date
CNN	red	10	01/05/2015
CNN	blue	15	12/12/2001
CBS	green	25	01/07/2014
NBC	yellow	67	01/07/2013

And this request:
Everything where the color is in {"red", "blue", "green"}
and site is in {"CNN", "NBC"}
and date is in the range defined above.

I would expect a result of 10. A count wouldn't help so much here, I would really need the sum.


Aladin, do you think you can help me out here again?

 

[Aladin Akyurek]

Things are getting complicated here. In another sum I'm trying to calculate I should only take dates that are in a certain range.

For example, this data range:
Higher than date(year(today()),month(today())-11),1)
AND
Lower than date(year(today()),month(today())-4),1)

Given the dataset:

site    product    money    date
CNN    red    10    01/05/2015
CNN    blue    15    12/12/2001
CBS    green    25    01/07/2014
NBC    yellow    67    01/07/2013

And this request:
Everything where the color is in {"red", "blue", "green"}
and site is in {"CNN", "NBC"}
and date is in the range defined above.

I would expect a result of 10. A count wouldn't help so much here, I would really need the sum.


Aladin, do you think you can help me out here again?

Which period exactly are you interested in: From 1-Jan-2015 to 1-Aug-2015?

 

[WouterP]

Which period exactly are you interested in: From 1-Jan-2015 to 1-Aug-2015?

I see I arranged some comma's incorrectly, excuse me.
Yes, you are right.

 

[Aladin Akyurek]

Which period exactly are you interested in: From 1-Jan-2015 to 1-Aug-2015?

I see I arranged some comma's incorrectly, excuse me.
Yes, you are right.

In E2 enter:

=DATE(YEAR(TODAY()),1,1)

In F2 enter:

=DATE(YEAR(TODAY()),8,1)

In G2 enter:

=SUMPRODUCT(C2:C5,--ISNUMBER(MATCH(A2:A5,{"CNN","NBC"},0)),
  --ISNUMBER(MATCH(B2:B5,{"red","blue","green"},0)),
  --($D$2:$D$5>=E2),--($D$2:$D$5<=F2))