# Count number of cells that contain red, blue or red and blue

1. ## Count number of cells that contain red, blue or red and blue

SS example:

Red
Green
Red, Blue, Green
Green, Red
Blue
Green

I need a function that will return the number of cells that contain red, blue, OR red and blue.

So the result of the function in this case would be 4.

The COUNTIF(data,"*red*")+COUNTIF(data,"*blue*") function that I am using returns 5, because that is how many times the two words appear. I only want it to count the cell that contains both words once.

Help would be greatly appreciated!

Thanks
Kelly

2. ## Re: Count number of cells that contain red, blue or red and blue

Try this...

With your data in the range A2:A7...

Use cells to hold the criteria. These cells must be a horizontal array.

C2 = Red
D2 = Blue

Then:

=SUMPRODUCT(--(MMULT(--(ISNUMBER(SEARCH(C2:D2,A2:A7))),{1;1})>0))

3. ## Re: Count number of cells that contain red, blue or red and blue

Getting #VALUE!

4. ## Re: Count number of cells that contain red, blue or red and blue

Here's a small sample file that demonstrates this.

kjshearon.xlsx

There are currently 1 users browsing this thread. (0 members and 1 guests)