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.
Quick Answer
Cells containing the word: =COUNTIF(A2:A9, "*late*"). Every occurrence, including repeats in one cell: =SUMPRODUCT((LEN(A2:A9)-LEN(SUBSTITUTE(LOWER(A2:A9),"late","")))/LEN("late")).
Steps to Count a Word With COUNTIF
- Click an empty cell where you want the result.
- Type
=COUNTIF(and select the range, for example A2:A9. - 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.
- Close the bracket and press Enter.
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")) |
Working with numbers? See rounding up in Google Sheets.
Using Excel? Read how to count a word in Excel.
Troubleshooting
- Count is too high: “*late*” also matches “latest” and “chocolate”. Use exact matching or a REGEXMATCH pattern with word boundaries for whole words.
- Result is 0: Check for extra spaces in cells; TRIM can clean them.
- Want word totals instead? See counting words in Google Sheets.
- Just need to find the word? See searching in Google Sheets. For Excel, see counting a word in Excel.
Frequently Asked Questions
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.
Summary
- Use COUNTIF with the word in quotes for exact matches.
- Add asterisks to count cells that contain the word.
- Use SUMPRODUCT with SUBSTITUTE to count every occurrence.
- Reference a cell to change the word easily.
Kermit Matthews is a freelance writer based in Philadelphia, Pennsylvania with more than a decade of experience writing technology guides. He has a Bachelor’s and Master’s degree in Computer Science and has spent much of his professional career in IT management.
He specializes in writing content about iPhones, Android devices, Microsoft Office, and many other popular applications and devices.