How to Count How Many Times a Word Appears in Google Sheets

To count how many times a word appears in Google Sheets, use COUNTIF. =COUNTIF(A2:A9, "late") counts cells that contain exactly “late”, and =COUNTIF(A2:A9, "*late*") counts cells that contain the word anywhere. COUNTIF counts cells, not repeats inside a cell, so use a SUMPRODUCT formula if a word can appear more than once in the same cell.

Steps to Count a Word With COUNTIF

  1. Click an empty cell where you want the result.
  2. Type =COUNTIF( and select the range, for example A2:A9.
  3. Type a comma and the word in quotes. Add asterisks around it, like “*late*”, to match cells where the word is part of longer text.
  4. Close the bracket and press Enter.
Spreadsheet with a COUNTIF formula counting how many cells contain a word
Asterisks count cells that contain the word anywhere.

Use a Cell Instead of Typing the Word

Put the word in D1 and use =COUNTIF(A2:A9, "*"&D1&"*"). Change D1 to count a different word without editing the formula.

Which Formula to Use

Goal Formula
Cells that are exactly the word =COUNTIF(A2:A9, "late")
Cells that contain the word =COUNTIF(A2:A9, "*late*")
Every occurrence, even repeats SUMPRODUCT with LEN and SUBSTITUTE
Case-sensitive count of cells =SUMPRODUCT(--REGEXMATCH(A2:A9, "late"))

Every occurrence, including repeats in one cell: =SUMPRODUCT((LEN(A2:A9)-LEN(SUBSTITUTE(LOWER(A2:A9),"late","")))/LEN("late")). Those counts are also easy to turn into a picture when you make a graph in Google Sheets from them.

Working with numbers? See rounding up in Google Sheets.

Using Excel? Read how to count a word in Excel.

When the Count Looks Off

What Else People Ask About COUNTIF

Is COUNTIF case-sensitive?

No. “Late” and “late” are counted the same. REGEXMATCH is case-sensitive by default.

Can I count more than one word?

Add COUNTIF results together, or use COUNTIFS when every condition must match in different columns.

Does COUNTIF work across several columns?

Yes. Use a range such as A2:C9 to count across all those cells.