How to sort a column by occurrence count in Excel?
- In the column next to the group insert the following formula
=COUNTIF(A$1:A$9, A1)
- Highlight the cells you want to sort.
- Click on the Data tab on top of the ribbon
- In the Sort section, select Sort. In the dialog box select the second column and then from highest to lowest.
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
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.