3

How do I get a word count on a range of cells?

(Note: In the examples below, the text I need a word count on is in a column called "English" and the text I want to count is in rows 12 to 103.)

I can get a word count on 1 cell using this formula:
=LEN(English12) - LEN(SUBSTITUTE(English12, " ", "")) + 1

I tried replacing "English12" with a range: 
=LEN(English12:English103) - LEN(SUBSTITUTE(English12:English103, " ", "")) + 1

...but this returns: #INVALID DATA TYPE

The formula below works, but as you can see only counts 3 cells. This isn't practical when there are hundreds of cells.
=(LEN(English12) - LEN(SUBSTITUTE(English12, " ", "")) + 1) + (LEN(English13) - LEN(SUBSTITUTE(English13, " ", "")) + 1) + (LEN(English14) - LEN(SUBSTITUTE(English14, " ", "")) + 1)

I found this formula in an Excel forum:
=SUMPRODUCT(LEN(TRIM(English12:English103))-LEN(SUBSTITUTE(English12:English103," ",""))+1)

...but it returns "#UNPARSEABLE"

Here are a couple variations I tried:
=SUMPRODUCT(LEN(TRIM([English]12:[English]103))-LEN(SUBSTITUTE([English]12:[English]103," ",""))+1)

Returns "#UNPARSEABLE"

=SUMPRODUCT(LEN(TRIM(English:English))-LEN(SUBSTITUTE(English:English," ",""))+1)

Returns "#UNPARSEABLE"

So again, How do I get a word count on a range of cells?

I'm new to Smartsheet, so if there's some way of doing this using reports I'd love to know. I don't currently have access to dashboards, but if that's a way of doing it, please let me know.

Thank you for any help!

Functionality

Comments

Interesting problem. 

 

=LEN(JOIN(English:English, " ")) - LEN(SUBSTITUTE(JOIN(English:English, " "), " ", "")) - COUNTIF(English:English, ISBLANK(@cell)) + 1

 

Let me know if that works

 

 

In reply to by [email protected]

This works!

I was able to modify to ignore rows 1-11 which contains text I don't want to count.

=LEN(JOIN(English12:English117, " ")) - LEN(SUBSTITUTE(JOIN(English12:English117, " "), " ", "")) - COUNTIF(English12:English117, ISBLANK(@cell)) + 1

Thank you so much

Also, in smartsheet all of your calculations are on the sheet level. Reports and Dashboards are only mechanisms to report and show data (there's some exceptions to dashboards where you can embed forms and sheets but that's another story)