# How to count cells that contain a whole word in Excel

Pad the text and the word with spaces so only whole words match: `=SUMPRODUCT(--ISNUMBER(SEARCH(" "&E1&" "," "&A2:A10&" ")))`.

**Excel:** `=SUMPRODUCT(--ISNUMBER(SEARCH(" "&E1&" "," "&A2:A10&" ")))` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Product |  |  | Word | pro |
| MacBook Pro 14 |  |  | Products |  |
| Pro stand |  |  |  |  |
| Professional headset |  |  |  |  |
| Surface Pro |  |  |  |  |
| Product sample |  |  |  |  |
| Earbuds pro |  |  |  |  |
| Approach shoes |  |  |  |  |
| Keyboard Pro TKL |  |  |  |  |
| Promo bundle |  |  |  |  |

Formula in E2: `=SUMPRODUCT(--ISNUMBER(SEARCH(" "&E1&" "," "&A2:A10&" ")))`

Result: 5

## How it works

Adding a space before and after each name, and before and after the word, means " pro " can only be found where "pro" stands alone, including at the start or end of the name. SEARCH ignores case, ISNUMBER turns "found" into TRUE, and SUMPRODUCT counts them.

All nine names contain the letters "pro", but only five use it as a word; "Professional", "Product", "Approach" and "Promo" don't count.

## Other ways

- `=SUMPRODUCT(--REGEXMATCH(A2:A10,"(?i)\b"&E1&"\b"))`: Google Sheets: `\b` marks a word boundary, which also handles punctuation such as "Pro,".
- `=COUNTIF(A2:A10,"*"&E1&"*")`: Counts the letters anywhere. (Returns 9: it also counts "Professional", "Product", "Approach" and "Promo".)

## Common mistakes

- Punctuation next to the word ("Pro," or "Pro.") stops the space-padded match. Replace common punctuation with spaces first: `SUBSTITUTE(A2:A10,","," ")`.
- `"* pro *"` in COUNTIF misses the word at the start or end of the text, which is why the formula pads the text itself.

## Questions

**Is there a whole-word option in Excel 365?**

Yes, REGEXTEST supports word boundaries: `=SUMPRODUCT(--REGEXTEST(A2:A10,"\b"&E1&"\b",1))`, where 1 ignores case.

**How do I count every occurrence of the word, not just cells?**

Use the space-padded text with the LEN and SUBSTITUTE method from the page on counting how many times a word appears in a range.

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-whole-word-matches