Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Calc: count cells that contain a given string, but are not equal to it

Imagine you wan to count cells containing the word "cat" in them.

The cells containing sentences like:

"Cats like milk" "Dogs don't like cats" "Every cat has nine lives" "A dog is a man's best friend"

You want to know how many cells contain the string "cat" - this should give a value of 3 for the above cells.

I know COUNTIF can match exact cell contents, but what about cells that contain other data aswell? I guess something like a regular expression would be handy, but I have no idea how to do that.

Also imagine you want to use another cell as source for the keyword to check against:

"cat" "dog" "man"

This should produce respectively:

3 2 1

How could this be done?