Counting number of cells that contain certain text but not cell tw
 

How can I count cells that contain certain words without counting same cell
twice.

e.g.

Column A
1 blue, green, white
2 white, yellow, red
3 red, blue, cyan
4 green, cyan, black
5 indigo, green, blue
6 yellow, red. black

How can I count the number of cells that contain blue, white or green

The answer in this case would be 4.

Many thanks

John

Counting number of cells that contain certain text but not cell tw
 

Shouldn't the correct answer be 5? If so, try...

=SUM(--(MMULT(--ISNUMBER(SEARCH({"blue","white","green"},A1:A6)),{ 1;1;1})
0))

or

=SUM(--(MMULT(--ISNUMBER(SEARCH(C1:E1,A1:A6)),TRANSPOSE(COLUMN(C1: E1)^0))
0))

....where C1:E1 contains blue, green, and white. Note that the second
formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
JRD wrote:

How can I count cells that contain certain words without counting same cell
twice.

e.g.

Column A
1 blue, green, white
2 white, yellow, red
3 red, blue, cyan
4 green, cyan, black
5 indigo, green, blue
6 yellow, red. black

How can I count the number of cells that contain blue, white or green

The answer in this case would be 4.

Many thanks

John