How to count cells that contain a whole word in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--ISNUMBER(SEARCH(" "&E1&" "," "&A2:A10&" ")))Pad the text and the word with spaces so only whole words match: =SUMPRODUCT(--ISNUMBER(SEARCH(" "&E1&" "," "&A2:A10&" "))).
- Result on the example
- 5 in
E2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
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.
=SUMPRODUCT(--ISNUMBER(SEARCH(" "&E1&" "," "&A2:A10&" ")))Result5
E1"pro"The value in E1.
" "&E1" pro"The & operator joins as text.
" "&E1&" "" pro "The & operator joins as text.
A2:A109 rows × 1 col: "MacBook Pro 14", "Pro stand", "Professional headset", "Surface Pro", …A range: 9 cells in one column.
" "&A2:A10{9 rows × 1 col} " MacBook Pro 14", " Pro stand", " Professional headset", " Surface Pro", …The & operator joins as text.
" "&A2:A10&" "{9 rows × 1 col} " MacBook Pro 14 ", " Pro stand ", " Professional headset ", " Surface Pro ", …The & operator joins as text.
SEARCH(" "&E1&" "," "&A2:A10&" "){9 rows × 1 col} 9, 1, #VALUE!, 9, …SEARCH: Position of text, not case-sensitive, allows wildcards.- find_text
" "&E1&" "" pro " - within_text
" "&A2:A10&" "{9 rows × 1 col} " MacBook Pro 14 ", " Pro stand ", " Professional headset ", " Surface Pro ", …
- find_text
ISNUMBER(SEARCH(" "&E1&" "," "&A2:A10&" ")){9 rows × 1 col} TRUE, TRUE, FALSE, TRUE, …ISNUMBER: TRUE if the value is a number.- value
SEARCH(" "&E1&" "," "&A2:A10&" "){9 rows × 1 col} 9, 1, #VALUE!, 9, …
- value
-ISNUMBER(SEARCH(" "&E1&" "," "&A2:A10&" ")){9 rows × 1 col} -1, -1, 0, -1, …Makes the value negative.
--ISNUMBER(SEARCH(" "&E1&" "," "&A2:A10&" ")){9 rows × 1 col} 1, 1, 0, 1, …Makes the value negative.
SUMPRODUCT(--ISNUMBER(SEARCH(" "&E1&" "," "&A2:A10&" ")))5SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--ISNUMBER(SEARCH(" "&E1&" "," "&A2:A10&" ")){9 rows × 1 col} 1, 1, 0, 1, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--ISNUMBER(SEARCH(" "&E1&" "," "&A2:A10&" "))). We ran it in Google Sheets mode on the same data and it returns 5.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(--REGEXMATCH(A2:A10,"(?i)\b"&E1&"\b"))Google Sheets | 5 | Google Sheets: \b marks a word boundary, which also handles punctuation such as "Pro,". |
| =COUNTIF(A2:A10,"*"&E1&"*") | 9Different result | 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.
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 cells that contain a whole word
E2, count the product names that contain the word in E1 as a whole word, not as part of a longer word.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.
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.