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

J

JRD

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
 
D

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top