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.
Leave a Comment