Count Total Words in a Range? Excel Formula Explained
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.
This page explains how to count the total number of words in a range of cells in Excel or Google Sheets. You enter the range (for example, A1:A10) and the formula returns the total word count across all cells. The core method uses SUMPRODUCT, LEN, and SUBSTITUTE, and this guide breaks down the formula, shows a worked example, and covers common pitfalls.
Formula
The standard formula to count words in a range is:
=SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(range," ",""))+1)
Here’s what each part does:
- LEN(range) – returns the number of characters in each cell of the range.
- SUBSTITUTE(range,” “,””) – removes all spaces from each cell.
- LEN(SUBSTITUTE(…)) – gives the character count without spaces.
- LEN(range) – LEN(SUBSTITUTE(…)) – counts the number of spaces in each cell.
- +1 – adds one per cell because the number of words equals spaces + 1 (assuming no leading/trailing spaces).
- SUMPRODUCT – sums the results across all cells in the range.
For example, if cell A1 contains “the quick brown fox”, LEN(A1) is 19, LEN(SUBSTITUTE(A1,” “,””)) is 15, the difference is 4, plus 1 gives 5 words. The same is done for every cell and then summed.
Worked Example
Suppose you have the following text in cells A1:A3:
- A1: “Hello world” (2 words)
- A2: “Excel word count” (3 words)
- A3: “This is a test” (4 words)
Using the formula with range A1:A3:
- LEN(A1)=11, LEN(A2)=17, LEN(A3)=14 → total characters = 42
- LEN(SUBSTITUTE(A1,” “,””))=10, LEN(SUBSTITUTE(A2,” “,””))=14, LEN(SUBSTITUTE(A3,” “,””))=11 → total without spaces = 35
- Spaces count = 42 – 35 = 7
- Add 1 for each cell (3 cells) → 7 + 3 = 10
So the total word count is 10. The formula in the calculator above returns 10 for the default range A1:A10, but with the example data it would return 10 as well.
How to Use the Formula in Excel or Google Sheets
To count words in a range in your spreadsheet:
- Select a cell where you want the result.
- Type
=SUMPRODUCT(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10," ",""))+1)but replace A1:A10 with your actual range. - Press Enter. The result appears as a number.
In Google Sheets, the same formula works. Note that this formula counts any sequence of characters separated by spaces as a word, so punctuation attached to a word (like “hello,”) is counted as part of that word.
Common Mistakes and How to Avoid Them
- Leading or trailing spaces – If a cell has extra spaces at the beginning or end, the formula overcounts. Use
TRIMinside the formula:=SUMPRODUCT(LEN(TRIM(range))-LEN(SUBSTITUTE(TRIM(range)," ",""))+1). - Empty cells – An empty cell returns 0 characters, so the formula adds 1 to it, incorrectly counting 1 word. To fix, use
=SUMPRODUCT((LEN(TRIM(range))>0)*(LEN(TRIM(range))-LEN(SUBSTITUTE(TRIM(range)," ",""))+1)). - Multiple spaces between words – The formula counts each space as a separator, so double spaces cause overcounts. Use
TRIMand also replace double spaces with single spaces usingSUBSTITUTEnested. - Line breaks – If text contains line breaks (CHAR(10)), they are not counted as spaces. Replace them with spaces first:
SUBSTITUTE(A1,CHAR(10)," ").
Alternative Methods
If you prefer a simpler approach, you can use a custom function in Google Sheets (Apps Script) or a VBA macro in Excel. However, the formula method works without any programming and is portable. Another alternative is to use Excel’s built-in Word Count feature, but that only works in the Word application, not in Excel. For a quick one-off count, you can copy the range into a text editor and use its word count tool.
Limitations of the Formula
The formula assumes words are separated by single spaces. It does not handle punctuation as separators (e.g., “word,word” would be counted as one word). It also counts numbers and symbols as words if they are surrounded by spaces. For more accurate linguistic word counting, you would need a more complex formula or a script. Additionally, the formula is case-insensitive and does not ignore formula results that are numbers – it treats them as text.
Frequently Asked Questions
How do I count words in a single cell?
Use =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1. This returns the number of words in cell A1.
How do I count words in a column excluding empty cells?
Use the array formula =SUMPRODUCT((LEN(TRIM(A1:A10))>0)*(LEN(TRIM(A1:A10))-LEN(SUBSTITUTE(TRIM(A1:A10)," ",""))+1)). The (LEN(TRIM(A1:A10))>0) part ensures empty cells are ignored.
Does this formula work in Google Sheets?
Yes, the same formula works in Google Sheets. Just enter it as a normal formula, no special array entry needed.
How do I count words with multiple spaces between them?
First normalize the spaces by using TRIM and then replace double spaces with a single space. For example: =SUMPRODUCT(LEN(TRIM(A1:A10))-LEN(SUBSTITUTE(TRIM(A1:A10)," ",""))+1) but this still overcounts if there are double spaces. A better way is to use SUBSTITUTE to collapse multiple spaces, but that requires a loop. A practical workaround is to use a helper column or a script.
Why does my formula return 1 for an empty cell?
Because the formula adds 1 for each cell regardless of content. To avoid that, use the condition LEN(TRIM(cell))>0 as shown above.
Can I count words in a range that contains numbers?
Yes, numbers are counted as words if they are separated by spaces. For example, “123 456” counts as 2 words.
Related
Formula used
in r | Range | - | A1:A10 | 0 out total | Total words | words | SUMPRODUCT(LEN(INDIRECT(r))-LEN(SUBSTITUTE(INDIRECT(r)," ",""))+1) | 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.