Google Sheets count how many cells contain a specific word / words?

Question

I'm trying to look at all cells in a set of columns/cells to count how many of them contain the word WORDHERE (in this example)

I've tried using:

=SUM(COUNTIF(A1:A100, "WORDHERE"))

However this finds 0 as the cell contains other words/letters/numbers, if the cell only contains WORDHERE it works perfectly.

I've tried using several regeexxtract and regexmatch including the actual word as you can see below:

=SUM(COUNTIF(A1:A100,REGEXEXTRACT(A1:A100, "WORDHERE")))

But again, it finds 0 matches.

What am I doing wrong?

Answer 1

You don't need SUM around the COUNTIF.

=COUNTIF(A1:A100, "*WORDHERE*") 

will work just as fine. The same can indeed be achieved with regexmatch in a more complicated formula:

=sum(ArrayFormula(N(regexmatch(A7:A, "WORDHERE"))))

Here N-function is used to 'convert' the boolean values (TRUE or FALSE) to 1 or 0.

Answer 2

Not exactly answering what you are doing wrong, but here is what you can do:

=COUNTIF(A1:A100, "*WORDHERE*")