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.
All articles TEXT Count total words in a range

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:
Solution examples
wondering if there is a formula that when the name in one cell changes, the formula in the other cell changes? for example: working on a calculating cost for harvesting grain crops based on acres, bushels harvested, and bushels harvested over x/bushels per acre. in cell B3 i want when cell A1 says "wheat harvest" it will subtract 20 from cell B2, but when cell A1 says "corn harvest" i want to subtract 40 from cell B2 is this possible? thanks
Solved by T. D. in 20 mins
Help, I need a formula to measure if date entered in column e, is between date in column a and date in column b. please
Solved by V. U. in 20 mins
Adding criteria to If(And( ),Sumproducts()
Solved by G. U. in 19 mins
I need section of text deleted in a column . Problem is the cell in each column Is html and seems it will be a challenge but if you can do this , you are genius
Solved by B. A. in 60 mins
Hi - I need a formula that can look at a Member Number (Column A) and report back if location (Column B) matches 1 of 6 locations, then looks to the date of occurance (Column N) but only adds back 1 value of repeating dates. I have this, but it is not coming back with the right answer... =SUMPRODUCT(('2017'!I$3:I$10000=$A223)*ISNUMBER(SEARCH({"2","6","58","69","76","97"},'2017'!N$3:N$10000))) Information is corporate private, so I won't be uploading file.
Solved by S. L. in 60 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

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.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc