Thread: Counting cells that matches any of multiple criteria

Hi!

I have following cells of text:

A3 "square is beautiful"
A4 "quad is nice"
A5 "cube is boring"
A6 "there are many squares"
A7 "not all quads are equal"
A8 "my ice cubes are melting"
A9 "add one sugar cube and two ice cubes"

I want to show value "1" in column "B" if the cell includes ANY of following words:

square
squares
quad
quads
cube
cubes

For example in the case of cell A6 ("there are many squares") I type following formula in the cell B6:

=COUNTIF(A6,"*square*")+COUNTIF(A6,"*quad*")+COUNTIF(A6,"*cube*")

and I am happy to see value "1" in the cell B6.

BUT in the case of cell A9 ("add one sugar cube and two ice cubes") the following formula in the cell B9:

=COUNTIF(A9,"*square*")+COUNTIF(A9,"*quad*")+COUNTIF(A9,"*cube*")

shows value "2" in the cell B9.

What to do to show value "1" in the cell B9 in this case?


JeePee

Thanks a lot!