Google Sheets - Using Query with Count

user_17626763 0
Viewed 104

I have data in I3:Z which I have assigned a named range to - "Data".

I have the following formula:

=QUERY(UNIQUE(FLATTEN(Data)))

Which returns a list of all the unique data entries.

I want to also add a count into the formula, so it shows how many times each data entry appears, and then sort the entire result by said number.

Thanks

google-query-language flatten counting google-sheets unique
2 Answers

Try

=query({query(flatten(data),"select Col1 where Col1 is not null"),sequence(COUNTA(data),1,1,0)},"select Col1,sum(Col2) group by Col1 label sum(Col2) 'Nb' ")
user_150193800

use:

=QUERY(FLATTEN(Data); 
 "select Col1,count(Col1)
  where Col1 is not null 
  group by Col1 
  order by count(Col1) desc 
  label count(Col1)''")
user_56326290
Related