How to count occurrences of a word in a cell in Excel

Q: How do I count the word across a whole column?

Wrap it in SUMPRODUCT: =SUMPRODUCT((LEN(A2:A7)-LEN(SUBSTITUTE(LOWER(A2:A7),"late","")))/LEN("late")).

Q: How do I count cells that contain the word at least once?

Use COUNTIF with wildcards: =COUNTIF(A2:A7,"*late*").

Formula (Excel and Google Sheets)

=(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($E$1),"")))/LEN($E$1)

Remove the word, compare lengths and divide by the word's length: =(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($E$1),"")))/LEN($E$1).

Result on the example: one result per row in B2, filled down to B7
Works in: Every current version
Google Sheets: Same formula

See it work

The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.

How it works

Removing every "late" shortens the text by 4 characters per match, so the length difference divided by LEN($E$1) is the number of matches. LOWER on both sides makes "Late" count too.

The last row shows the limit: "latest" contains "late", so it counts.

=(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($E$1),"")))/LEN($E$1)

Result2

A2"Delivery was late, then late again"
The value in A2.
LEN(A2)34
LEN: Number of characters.
- textA2"Delivery was late, then late again"
LOWER(A2)"delivery was late, then late again"
LOWER: Converts to lowercase.
- textA2"Delivery was late, then late again"
$E$1"late"
The value in E1.
LOWER($E$1)"late"
LOWER: Converts to lowercase.
- text$E$1"late"
SUBSTITUTE(LOWER(A2),LOWER($E$1),"")"delivery was , then again"
SUBSTITUTE: Replaces matching text.
- textLOWER(A2)"delivery was late, then late again"
- old_textLOWER($E$1)"late"
- new_text""
LEN(SUBSTITUTE(LOWER(A2),LOWER($E$1),""))26
LEN: Number of characters.
- textSUBSTITUTE(LOWER(A2),LOWER($E$1),"")"delivery was , then again"
LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($E$1),""))8
The - operator subtracts.
LEN($E$1)4
LEN: Number of characters.
- text$E$1"late"
LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($E$1),"")))/LEN($E$1)2
The / operator divides.

In Google Sheets

The same formula works in Google Sheets: =(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER($E$1),"")))/LEN($E$1). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=(LEN(A2)-LEN(SUBSTITUTE(A2,$E$1,"")))/LEN($E$1)	2, 0, 2…Different result	Case-sensitive version. "Late order, late refund, late reply" gives 2, because "Late" with a capital is skipped.

Common mistakes

Without dividing by the word's length you get the number of characters removed (8 instead of 2).
Parts of longer words match too: "late" is found in "latest" and "chocolate". To count whole words only, pad with spaces and remove punctuation first: " "&SUBSTITUTE(LOWER(A2),","," ")&" " and search for " late ".

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Count occurrences of a word in a cell

In B2, count how many times the word in E1 appears in the review, ignoring case, and fill down to B7.

Questions

How do I count the word across a whole column?

Wrap it in SUMPRODUCT: =SUMPRODUCT((LEN(A2:A7)-LEN(SUBSTITUTE(LOWER(A2:A7),"late","")))/LEN("late")).

How do I count cells that contain the word at least once?

Use COUNTIF with wildcards: =COUNTIF(A2:A7,"*late*").

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.