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

[Guest]

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

 

[Domenic]

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!

 

[Domenic]

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!