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!
