How to count how many times a word appears in a range in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT((LEN(A2:A9)-LEN(SUBSTITUTE(LOWER(A2:A9),LOWER(D1),"")))/LEN(D1))Measure how much shorter the text gets when the word is removed: =SUMPRODUCT((LEN(A2:A9)-LEN(SUBSTITUTE(LOWER(A2:A9),LOWER(D1),"")))/LEN(D1)).
- Result on the example
- 7 in
D2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.
How it works
SUBSTITUTE deletes every copy of the word, so the text gets shorter by the word's length once per copy. Dividing the drop in length by LEN(D1) gives the number of copies in each comment, and SUMPRODUCT adds them up across the range.
SUBSTITUTE is case-sensitive, so both sides go through LOWER and "Late" counts. Two comments use the word twice, which is why the total is 7 while only 5 comments contain it.
=SUMPRODUCT((LEN(A2:A9)-LEN(SUBSTITUTE(LOWER(A2:A9),LOWER(D1),"")))/LEN(D1))Result7
A2:A98 rows × 1 col: "Delivery was late and the box was damaged", "Late again. Second late delivery this month", "Great service", "Driver arrived late", …A range: 8 cells in one column.
LEN(A2:A9){8 rows × 1 col} 41, 43, 13, 19, …LEN: Number of characters.- text
A2:A98 rows × 1 col: "Delivery was late and the box was damaged", "Late again. Second late delivery this month", "Great service", "Driver arrived late", …
- text
LOWER(A2:A9){8 rows × 1 col} "delivery was late and the box was damaged", "late again. second late delivery this month", "great service", "driver arrived late", …LOWER: Converts to lowercase.- text
A2:A98 rows × 1 col: "Delivery was late and the box was damaged", "Late again. Second late delivery this month", "Great service", "Driver arrived late", …
- text
D1"late"The value in D1.
LOWER(D1)"late"LOWER: Converts to lowercase.- text
D1"late"
- text
SUBSTITUTE(LOWER(A2:A9),LOWER(D1),""){8 rows × 1 col} "delivery was and the box was damaged", " again. second delivery this month", "great service", "driver arrived ", …SUBSTITUTE: Replaces matching text.- text
LOWER(A2:A9){8 rows × 1 col} "delivery was late and the box was damaged", "late again. second late delivery this month", "great service", "driver arrived late", … - old_text
LOWER(D1)"late" - new_text
""
- text
LEN(SUBSTITUTE(LOWER(A2:A9),LOWER(D1),"")){8 rows × 1 col} 37, 35, 13, 15, …LEN: Number of characters.- text
SUBSTITUTE(LOWER(A2:A9),LOWER(D1),""){8 rows × 1 col} "delivery was and the box was damaged", " again. second delivery this month", "great service", "driver arrived ", …
- text
LEN(A2:A9)-LEN(SUBSTITUTE(LOWER(A2:A9),LOWER(D1),"")){8 rows × 1 col} 4, 8, 0, 4, …The - operator subtracts.
LEN(D1)4LEN: Number of characters.- text
D1"late"
- text
LEN(A2:A9)-LEN(SUBSTITUTE(LOWER(A2:A9),LOWER(D1),"")))/LEN(D1){8 rows × 1 col} 1, 2, 0, 1, …The / operator divides.
SUMPRODUCT((LEN(A2:A9)-LEN(SUBSTITUTE(LOWER(A2:A9),LOWER(D1),"")))/LEN(D1))7SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
LEN(A2:A9)-LEN(SUBSTITUTE(LOWER(A2:A9),LOWER(D1),"")))/LEN(D1){8 rows × 1 col} 1, 2, 0, 1, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT((LEN(A2:A9)-LEN(SUBSTITUTE(LOWER(A2:A9),LOWER(D1),"")))/LEN(D1)). We ran it in Google Sheets mode on the same data and it returns 7.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =COUNTIF(A2:A9,"*"&D1&"*") | 5Different result | Counts comments that contain the word at least once. Returns 5: each comment counts once however many times it uses the word. |
Common mistakes
- The formula counts the letters wherever they appear, so "late" is also found inside "plate" and "chocolate". To count whole words only, pad with spaces: replace
LOWER(A2:A9)with" "&LOWER(A2:A9)&" "and the word with" "&LOWER(D1)&" "; punctuation next to the word still needs handling. - Leaving out LOWER makes the count case-sensitive: "Late" at the start of a sentence would be missed.
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 how many times a word appears in a range
D2, count every time the word in D1 appears in the comments, including several times in one comment.Questions
How do I count a word in a single cell?
Use the same formula without SUMPRODUCT: =(LEN(A2)-LEN(SUBSTITUTE(LOWER(A2),LOWER(D1),"")))/LEN(D1).
How do I count a single character, like commas?
The word length is 1, so drop the division: =LEN(A2)-LEN(SUBSTITUTE(A2,",","")).
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.