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

dbeck

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!

L_123

Interesting problem. 

 

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

 

Let me know if that works

 

 

L_123

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)

dbeck

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

Billy Rock

This has been so helpful. While I don't fully understand the long formula I am now able to get a result other than 0. Sadly, my result is now a large negative number. And because of my lack of knowledge I do not know why. With the explanation and screenshots below I hope I can get some more guidance.

I am trying to get a count with cross sheet formulas of the words "Call", "Email", and "Mtg Debrief". The count will be happening on a sheet that is used to consolidate data from a data dump sheet. I hope you can help me out.

On my data dump sheet I want to count how often the words above appear in the "Touch Type" column. We are trying to get more of an accurate idea of our main types of communication with our clients. The Touch Type column and Submitter/Submitter Count are on separate sheets. So the need for cross sheet formula's is necessary.

Thanks so much for your time and conversation!

Billy Rock

FYI - this is the formula that is giving me the large negative number

=LEN(JOIN({Touch Log - Detail Range 4}, " ")) - LEN(SUBSTITUTE(JOIN({Touch Log - Detail Range 2}, " "), " ", "")) - COUNTIF({Touch Log - Detail Range 4}, ISBLANK(Submitter@row)) + 1