Showing top 5 most frequent values and counts of each from a data range

Question

I have a Google sheet with text strings listed down columns D through G. I want to, on another sheet, display a list of the top, say, 5 most frequent unique values from the first sheet in descending order and also display the number of times each one shows up.

How can I do this?

Tried finding something online already to do this, but stumped how to have the frequency count show up and how to handle the values appearing across a range of multiple columns.

Answer 1

I found a way to do this.

First, I brought all the unique values from Columns D-G of Sheet1 to a single column in Sheet 2 with this:

=SORT(UNIQUE({'Sheetname'!D2:D;'Sheetname'!E2:E;'Sheetname'!F2:F;'Sheetname'!G2:G}))

So that sorted the names into alphabetical order (Col B) and number of instances of each in the next column (Col C). Then, on that Sheet2, in another column, I used this to pull the top 5.

=query(B2:C,"Select B, C where B<>'' Order by C Desc limit 5")

Answer 2

• you can do it all in one go like this:

  =ARRAYFORMULA(QUERY(TRANSPOSE(SPLIT(CONCATENATE(
   IF(Sheet1!D:G<>"", "♦"&Sheet1!D:G, )), "♦")), 
   "select Col1,count(Col1) 
    group by Col1 
    order by count(Col1) desc 
    limit 5 
    label count(Col1)''", 0))