# How to count how many times a word appears in a range in Excel

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))`.

**Excel:** `=SUMPRODUCT((LEN(A2:A9)-LEN(SUBSTITUTE(LOWER(A2:A9),LOWER(D1),"")))/LEN(D1))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Customer comment |  | Word | late |
| Delivery was late and the box was damaged |  | Times used |  |
| Late again. Second late delivery this month |  |  |  |
| Great service |  |  |  |
| Driver arrived late |  |  |  |
| Arrived on time |  |  |  |
| Too late to cancel, then refund was late too |  |  |  |
| No complaints |  |  |  |
| Late fee charged twice |  |  |  |

Formula in D2: `=SUMPRODUCT((LEN(A2:A9)-LEN(SUBSTITUTE(LOWER(A2:A9),LOWER(D1),"")))/LEN(D1))`

Result: 7

## 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.

## Other ways

- `=COUNTIF(A2:A9,"*"&D1&"*")`: 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.

## 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,",",""))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/count-how-many-times-a-word-appears-in-a-range