1

I am trying to display the 5 most commonly appearing text values in a column in Google Sheets.

The following will give me the most common value in column A:

=ArrayFormula(INDEX(A:A,MODE(MATCH(A:A,A:A,0))))

And this will give me the second most common value in column A, assuming that the formula above is in cell G2 (whose result is being excluded):

=ArrayFormula(INDEX(A:A,MODE(IF(A:A<>G2,MATCH(A:A,A:A,0)))))

How can I get the third, fourth, fifth, etc most common values? Something like this does not work:

=ArrayFormula(INDEX(A:A,MODE(IF(A:A<>G2:G3,MATCH(A:A,A:A,0)))))

Basically, I need to exclude multiple values from that calculation. Any ideas?

0

=QUERY(QUERY(B:B,
 "select B,count(B) 
  where B is not null 
  group by B 
  order by count(B) desc 
  label count(B)''", 0), 
 "select Col1
  limit 5", 0)

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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