Google Spreadsheet generating a list of the most frequently used words in an entire sheet

Question:

Hey guys,

I have a spreadsheet made up of columns that list the top 100 search terms on a website for each month of the year. Is there a formula I can use to search all of the columns and generate a list of the overall top 20 terms?

Any help would be greatly appreciated.

Thanks!

Solution:

Screenshot of "Sheet2":

First of all to get the unique values of Sheet1, I have the following formula in cell A1:
=unique({Sheet1!B3:B102;Sheet1!C3:C102;Sheet1!D3:D102;Sheet1!E3:E102;Sheet1!F3:F102;Sheet1!G3:G102;Sheet1!H3:H102;Sheet1!I3:I102;Sheet1!J3:J102;Sheet1!K3:K102;Sheet1!L3:L102;Sheet1!M3:M102;Sheet1!N3:N102;Sheet1!O3:O102;Sheet1!P3:P102;Sheet1!Q3:Q102})

and then to count the values in Column A, I have the following formula in cell B1:
=arrayformula(if(len(A1:A),countif(Sheet1!B3:Q102,A1:A),))

and then to sort the values of Column A and B in descending order of Column B, I have the following formula in cell E1:
=sort(A:B,2,false)


========================================


Alternatively you can also have the solution with a following single advanced formula:

=query(arrayformula({unique({Sheet1!B3:B102;Sheet1!C3:C102;Sheet1!D3:D102;Sheet1!E3:E102;Sheet1!F3:F102;Sheet1!G3:G102;Sheet1!H3:H102;Sheet1!I3:I102;Sheet1!J3:J102;Sheet1!K3:K102;Sheet1!L3:L102;Sheet1!M3:M102;Sheet1!N3:N102;Sheet1!O3:O102;Sheet1!P3:P102;Sheet1!Q3:Q102}),countif(Sheet1!B3:Q102,unique({Sheet1!B3:B102;Sheet1!C3:C102;Sheet1!D3:D102;Sheet1!E3:E102;Sheet1!F3:F102;Sheet1!G3:G102;Sheet1!H3:H102;Sheet1!I3:I102;Sheet1!J3:J102;Sheet1!K3:K102;Sheet1!L3:L102;Sheet1!M3:M102;Sheet1!N3:N102;Sheet1!O3:O102;Sheet1!P3:P102;Sheet1!Q3:Q102}))}),"select * order by Col2 desc",0)

Screenshot of "Sheet3":