Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

#### Post your problem and you'll get expert help in seconds

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

# Count total words in a range

If you need to count Count total words in a range, this article will show you exactly how to solve the problem. By using the combination of SUMPRODUCT, LEN AND SUBSTITUTE function with logical.

## Generic Formula

`=SUMPRODUCT(LEN(TRIM(rng))-LEN(SUBSTITUTE(rng," ",""))+1)`

• “rng” is the range of cells containing the text.
• LEN on the left calculate words but after extra spaces (beginning and end of text) have been slashed TRIM
• while LEN on the right counts text length without any space.
• The subtraction of the two LENs plus number 1 is summed up to get the total word count.

### Example

Firstly, if you have a number of sentences in a number of individual cells, you can easily their count using this formula. Check out how we did it example:

Figure 1. Example 1 of SUMPRODUCT/LEN/TRIM/ SUBSTITUTE Functions

The highlighted cell(C11) is:

`=SUMPRODUCT(LEN(TRIM(B5:B9))-LEN(SUBSTITUTE(B5:B9," ",""))+1)`

## How does the Formula work?

The SUBSTITUTE gets rid of all spaces from the sentences or text while LEN on the right calculates the length of the sentences of text without spaces. To get the actual count of words in this range of cells (B5:B9), the result from LEN’s calculation is subtracted from the count of the text with space which is a result on the LEN on the left. Furthermore, the subtraction gives us the number of spaces which is then added number 1. That is because we know that the number of words is always equal to the number of spaces plus 1.

## Notes

• TRIM get rid of extra spaces especially at the beginning and the end of each text in the cell for the LEN on the left.
• Finally, SUMPRODUCT will then sum the list to return the total count of words in a range of cells.

### Did this post not answer your question? Get a solution from connecting with the expert. 