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

google-sheets google-sheets-formula
Ryflex picture Ryflex · Nov 6, 2016 · Viewed 15.3k times · Source

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

Jeremy Kahan picture Jeremy Kahan · Nov 6, 2016

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

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

Related questions

Conditional formatting based on another cell's value

I'm using Google Sheets for a daily dashboard. What I need is to change the background color of cell B5 based on the value of another cell - C5. If C5 is greater than 80% then the background color is green …

Read More
Count cells that contain any text

I want to count the cells that contain anything within a range. Any cell that contain text, or numbers or something else should do a plus one in my result-cell. I found this function, countif(range; criteria) but this doesn't …

Read More
Get the last non-empty cell in a column in Google Sheets

I use the following function =DAYS360(A2, A35) to calculate the difference between two dates in my column. However, the column is ever expanding and I currently have to manually change 'A35' as I update my spreadsheet. Is there …

Read More