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.


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:


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")

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

     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))


Not the answer you're looking for? Browse other questions tagged or ask your own question.