Count Specific Words in a Range? Excel Formula
The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.
To count specific words in a range in Excel, use the formula =(LEN(range)-LEN(SUBSTITUTE(range,"word","")))/LEN("word") as an array formula, or use SUMPRODUCT to avoid Ctrl+Shift+Enter. This counts every occurrence of the word in all cells of the range, not just the number of cells that contain it.
Formula
The formula to count occurrences of a word in a range is:
=(LEN(range) - LEN(SUBSTITUTE(range, word, ""))) / LEN(word)
Where:
- range – the cell range you want to search (e.g., A1:A10).
- word – the exact text you are counting (e.g., “apple”).
- LEN() – returns the number of characters in a text string.
- SUBSTITUTE() – replaces all occurrences of a specific text in a string with new text (here, with an empty string “”).
The logic: subtract the length of the range after removing the word from the original length, then divide by the length of the word. The result is the total number of times the word appears.
Worked Example
Using the default values from the calculator: range text is “apple, banana, apple pie” and word is “apple”.
- Original length:
LEN("apple, banana, apple pie")= 24 - Remove the word:
SUBSTITUTE("apple, banana, apple pie","apple","")= “, banana, pie” (note the extra spaces). Length = 14 - Difference: 24 – 14 = 10
- Divide by word length: 10 / 5 = 2
The result is 2, matching the calculator output.
When to Use This Formula
Use this formula when you need to count how many times a specific word appears across multiple cells, not just how many cells contain it. It is useful for:
- Counting keywords in survey responses or feedback forms.
- Tracking occurrences of a product name in a list of orders.
- Analyzing text data in spreadsheets for frequency analysis.
If you only need to count cells that contain the word (even if it appears multiple times), use COUNTIF(range,"*word*") instead.
How to Enter the Formula Correctly
In Excel, the formula works as an array formula if entered with Ctrl+Shift+Enter in older versions. In Excel 365 and Excel 2021, it works normally because dynamic arrays are default. To avoid array entry entirely, use SUMPRODUCT:
=SUMPRODUCT((LEN(range)-LEN(SUBSTITUTE(range,"word","")))/LEN("word"))
This version does not require special key combinations and works in all Excel versions.
Common Mistakes and Pitfalls
- Case sensitivity: The formula is case-sensitive. To count both “Apple” and “apple”, use
SUBSTITUTE(UPPER(range),UPPER("word"),""). - Partial matches: The formula counts the word as a substring. For example, counting “cat” will also count “category”. To avoid this, add spaces around the word:
" cat ", but this fails at the start/end of a cell or with punctuation. - Empty cells: Empty cells do not affect the result because LEN of an empty cell is 0.
- Special characters: If the word contains wildcards like * or ?, SUBSTITUTE treats them literally, so no issue, but COUNTIF would treat them as wildcards.
Limitations and Alternatives
This formula counts exact text strings, not whole words. For whole-word counting, you need a more complex formula or VBA. Also, it does not handle multiple different words at once – you would need to sum separate formulas. For very large ranges, the formula may slow down because it processes each cell’s full text. An alternative is to use Power Query or a pivot table with text splitting, but those require more setup.
Frequently Asked Questions
How do I count a word in a range ignoring case?
Wrap both the range and the word in UPPER() inside the formula: =SUMPRODUCT((LEN(UPPER(range))-LEN(SUBSTITUTE(UPPER(range),UPPER("word"),"")))/LEN("word")). This converts all text to uppercase before counting, so case differences are ignored.
Why does my formula return a decimal or #VALUE! error?
A decimal result usually means the word is not found in some cells, but the division still works. If you get #VALUE!, check that the range does not contain errors, and that the word argument is not a cell reference that contains a number – LEN expects text. Also ensure you entered the formula as an array formula (Ctrl+Shift+Enter) in older Excel versions.
Can I count multiple words at once?
Yes, by adding multiple SUMPRODUCT formulas together. For example, to count both “apple” and “banana”, use: =SUMPRODUCT((LEN(range)-LEN(SUBSTITUTE(range,"apple","")))/LEN("apple")) + SUMPRODUCT((LEN(range)-LEN(SUBSTITUTE(range,"banana","")))/LEN("banana")).
Does this formula count cells or occurrences?
It counts occurrences. If a cell contains “apple apple”, it counts as 2. If you need to count cells that contain the word at least once, use COUNTIF(range,"*apple*").
How do I count whole words only, not substrings?
There is no simple built-in formula. One approach is to add spaces around the word and also wrap the range with spaces: =SUMPRODUCT((LEN(" "&range&" ")-LEN(SUBSTITUTE(" "&range&" "," "&word&" ","")))/LEN(" "&word&" ")). This works for most punctuation but may fail with commas or periods adjacent to the word. For robust whole-word counting, consider using VBA or Power Query.
Related
Formula used
in r | Range text | - | apple, banana, apple pie | 0 in w | Word to count | - | apple | 0 out count | Occurrences | - | (LEN(r)-LEN(SUBSTITUTE(r,w,"")))/LEN(w) | 0
Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.