COUNTIFS with OR

[Mark Talbot]

Hi
I have a sheet as below which is around 1000 rows long.
Column A contains upto 100 different site codes. Column B always has 2
letters and there are 40 different combinations of the 2 letters
possible.

I am trying the following (which does not work obviously):

COUNTIFS(A:A,1223,B:B,("Red" OR "Blue" OR etc etc))

A B
1 1223 Red
2 1223 Blue
3 1234 Blue
4 1223 Green
5 1235 Orange

I know I could use COUNTIF+COUNTIF+.... multiple times but I would
need 20 added together and want to simplify the formula.

I am now losing the will to live looking for a solution so if anyone
has any ideas they would be greatly appreciated.

Thank
Mark.

 

[Mark Talbot]

Sorry - ammended with what I actually meant..

Hi
I have a sheet as below which is around 1000 rows long.
Column A contains upto 100 different site codes.
Column B contains upto 40 different colours.

I am trying to do the following (which does not work obviously):

COUNTIFS(A:A,1223,B:B,("Red" OR "Blue" OR etc etc))

     A       B
1  1223  Red
2  1223  Blue
3  1234  Blue
4  1223  Green
5  1235  Orange

I know I could use COUNTIF+COUNTIF+.... multiple times but I would
need 20 added together and want to simplify the formula.

I am now losing the will to live looking for a solution so if anyone
has any ideas they would be greatly appreciated.

Thanks
Mark

 

[Claus Busch]

Hi Mark,

Am Tue, 3 Apr 2012 11:49:20 -0700 (PDT) schrieb Mark Talbot:

COUNTIFS(A:A,1223,B:B,("Red" OR "Blue" OR etc etc))

A B
1 1223 Red
2 1223 Blue
3 1234 Blue
4 1223 Green
5 1235 Orange

for B1:B100 is Red or Blue:
=SUMPRODUCT(((B1:B100="Red")+(B1:B100="Blue"))*(A1:A100=1223))


Regards
Claus Busch

 

[Claus Busch]

Hi Mark,

Am Tue, 3 Apr 2012 20:57:40 +0200 schrieb Claus Busch:

for B1:B100 is Red or Blue:
=SUMPRODUCT(((B1:B100="Red")+(B1:B100="Blue"))*(A1:A100=1223))

or insert headers for your data and then insert PivotTable.
Drag the numbers to the rows and the colors to columns and values.


Regards
Claus Busch

 

[Mark Talbot]

Hi Mark,

Am Tue, 3 Apr 2012 11:49:20 -0700 (PDT) schrieb Mark Talbot:



for B1:B100 is Red or Blue:
=SUMPRODUCT(((B1:B100="Red")+(B1:B100="Blue"))*(A1:A100=1223))

Regards
Claus Busch

Hi Claus
Thank you for your swift reply however, I am trying to check if any of
20 possible colours are against a site code ie

COUNTIFS(A:A,1223,B:B,("Red" OR "Blue" OR "Green" OR "Blue" OR "Black
OR "Orange"..........etc etc))

Any ideas?

Thanks
Mark

 

[Claus Busch]

Hi Mark,

Am Tue, 3 Apr 2012 12:04:28 -0700 (PDT) schrieb Mark Talbot:

COUNTIFS(A:A,1223,B:B,("Red" OR "Blue" OR "Green" OR "Blue" OR "Black
OR "Orange"..........etc etc))

have a look:
http://www.claus-busch.de/Excel/Mark.xls
In sheet(1) there is a name for the color (Excel4 Macro)
In sheet(2) you can filter the colors and work with subtotal


Regards
Claus Busch