CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Ryflex
Ryflex

Reputation: 5779

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

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?

Upvotes: 12

Views: 18783

Answers (2)

Jeremy Kahan
Jeremy Kahan

Reputation: 3826

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

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

Upvotes: 14

JPV
JPV

Reputation: 27292

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.

Upvotes: 3

Related Questions