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.

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

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I need a formula that will tell me how many total shares were for articles that were less than 1000 words and for greater than 1000 words. I have one column with the total number of shares and another with the total word count.
Solved by X. Y. in 23 mins
In cell H7, enter a database function to count the total number of in-state early admissions. Use the range L9:M10 for the criteria range and C13 for the field.
Solved by C. C. in 19 mins
I have a formula question: Is there a way to count consecutive values in a column range, and then once you have an empty cell, restart the total count with the next cell in the column?
Solved by M. L. in 27 mins

Leave a Comment

avatar