Ask Your Question

Revision history [back]

As you mentioned, COUNTIF supports regular expressions - which depends on a setting.

So given the use of regular expressions is enabled, and also that COUNTIF uses case-insensitive comparison, using the following formula will count all "Cat/cat/cAts/..." that are whole words somewhere in texts in column A:

=COUNTIF(A:A; ".*\bcats?\b.*")

If you don't need to limit yourself to only count whole words, but also want to count words like "concatenate", then you'd remove both \b from the expression. If you want to find whole word given in B1, then you might use this:

=COUNTIF(A:A; "*.\b\Q" & B1 & "\E\b.*")

As you mentioned, COUNTIF supports regular expressions - which depends on a setting.

So given the use of regular expressions is enabled, and also that COUNTIF uses case-insensitive comparison, using the following formula will count all "Cat/cat/cAts/..." that are whole words somewhere in texts in column A:

=COUNTIF(A:A; ".*\bcats?\b.*")

If you don't need to limit yourself to only count whole words, but also want to count words like "concatenate", then you'd remove both \b from the expression. If you want to find whole word given in B1, then you might use this:

=COUNTIF(A:A; "*.\b\Q" ".*\b\Q" & B1 & "\E\b.*")