< Go Back

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.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar