اكسل

Now let’s come to the next level.

And here you need to count words from a range of cells instead of a single cell.

The good news is you just need to use the same formula (just a simple change) which you have used above.

And the formula will be:

=SUMPRODUCT(LEN(A1:A11)-LEN(SUBSTITUTE(A1:A11," ",""))+1)

In the above formula, A1:A11 is the range of cells and when you enter the formula it returns 77 in the result.

here's how it works

This formula works in the same way as the first method works but just a small advanced. The only difference is you have wrapped it in SUMPRODUCT and refer to the entire range instead of a single cell.

Do you remember that SUMPRODUCT can take arrays? So when you use it, it returns an array where you have a count of words for each cell.

And in the end, it sums those counts and tells you the count of words in the column.