How to get a count of words appearing in a column of text fields, sorted by volume

[Doug]

I'm essentially wanting to create a word cloud, though it doesn't have to be in that form. A Treemap would be fine. Even a listing of the most frequently used terms, with an associated metric, like engagements.

So it would be a chart that has counted the frequency of all the words in a series of text fields.

Would I need to start with a COUNTIF formula in Sheets? 

Would appreciate any input on this.

thx

doug

[Doug]

Meant to add, this is a worksheet.

https://groups.google.com/forum/#!topic/google-visualization-api/kYqjyDtbmqg

And for the example, I'd like to count the most frequently used words in the text fields in the "Message" column.

thanks

[Ray Thomas]

The quickest way I know how to do this is with a query.. Suppose you have your list of words in column A. In cell B2 you can use

=query(A2:A,"select A, count(A) where A != '' group by A order by count(A) desc")

That adds two new columns, a list with the unique words and in the next column the number count of those words sorted highest first.

You can also do it using formulas, but I don't see how it can be done in one step.

At the top of a new column (in my case D) put =unique(A2:A) that wil give you the list of unique words. In the next column put =COUNTIF($A$2:A,D2) and copy that down the list of unique words, that gives you the count of each of the unique words.

In the column after that put =sort(D2:E,2,FALSE) which creates of copy of columns D and E, but sorted in descending order of column E.

Here's a Google Sheet showing all of the above - https://docs.google.com/spreadsheets/d/1OOo9dfSW6ppYKt7PJUHCWGrYC2JDGQpCYmFjHhZibeA/edit#gid=0