10

Let's say I have a column like this:

Dog
Bird
Cat
Dog
Dog
Dog
Bird
Cat
Bird

It has 4 Dog, 3 Bird and 2 Cat

I want to sort this column descending by these count numbers.

I mean result should be this when I sort it:

Dog
Dog
Dog
Dog
Bird
Bird
Bird
Cat
Cat

How can I do that in Excel 2007?

  • 1
    Why not just use a pivot table? – Raystafarian Jul 27 '13 at 15:33
  • 1
    I don't know how to do it. – Someone Jul 27 '13 at 15:34
  • 1
    Try it out, go to insert - pivot table. Then select your range. Drag your header down to the sum values and change it to count and then drag the header to row labels it will make the table, then change the sorting options. It may not be exactly what you want – Raystafarian Jul 27 '13 at 15:36
10
  1. In the column next to the group insert the following formula

=COUNTIF(A$1:A$9, A1)

  1. Highlight the cells you want to sort.
  2. Click on the Data tab on top of the ribbon
  3. In the Sort section, select Sort. In the dialog box select the second column and then from highest to lowest.

enter image description here

  • 1
    Edited the question to make it more clear, it's not about alphabetically. – Raystafarian Jul 27 '13 at 15:32
  • 1
    Please re-read question – Someone Jul 27 '13 at 15:33
1

For me (using Excel 2016 on Win8) this answer doesn't work. It resorts the FORMULAS which now don't refer to the field in the same row...

The way to avoid this is to use =COUNTIF(Sheet2!$A$1:$A$100,INDEX(A:A,ROW()))

Solution explained in Excel Forum - Sorting data containing COUNTIF formulas

1

For @wbeard52's solution - Excel 2016 on Windows 10, instead of , it should be a ; as delimiter. Like this:

=COUNTIF(A$1:A$9;A1)

Tip: If you have many rows, write the two first rows like this:

=COUNTIF(A$1:A$10000;A1)
=COUNTIF(A$1:A$10000;A2)

Then copy these two cells, mark cell 3 to 10000 in this case and then paste the code. I did this with 150k + rows and it worked like a charm. However I had to restart Excel after setting the formula for the correct values to show up.

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.