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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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))
Click to expand...

....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!
 
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))
Click to expand...

....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!
 
Back
Top