1

So, I know there is a easy way to sort unique values in alphabetic order using

SORT(UNIQUE(),1,TRUE)

but i'm trying to order based on how many times a item has appeared.

exemple, if had a column like

  1. Apple
  2. Apple
  3. Banana
  4. Banana
  5. Banana
  6. Lemon

I would like a return like:

  1. Banana
  2. Apple
  3. Lemon
1

2 Answers 2

1

With your list in A1:A6 you can try:

=unique(sort(A1:A6,countif(A1:A6,A1:A6),0))

N.B I've just realised you've tagged the question with Excel as well as Google Sheets - this formula only works in Google Sheets as the Excel implementation of SORT is different.

2
  • This works great thanks! (i tagged excel because a answer in excel could solve my current problem as well)
    – halegua
    Jan 24 at 23:13
  • Consider marking the question as answered 😉
    – Martín
    Jan 25 at 5:29
0

One way is to use the query() function:

=query(A1:A, "select A, count(A) group by A order by count(A) desc", 0)

This has the benefit that it also shows the counts per value.

If the column has a header row, you can show that header on top of the results by replacing the 0 with a 1.

In the event the column includes blank values, you can leave out that count like this:

=query(A1:A, "select A, count(A) where A is not null group by A order by count(A) desc", 0)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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