Ranking items in excel.
While searching for how to rank items I wasn’t happy with the solutions I found, they weren’t intuitive and none worked well for ranking within a group. I think I have a solution that is both readable and works well for many types of rankings.
The countif function typically counts items that are like one another, which makes it great for group ranks, to further extend countif you can use countifs for groups which may involve multiple columns to determine groups, or even use it for more complex rankings as I’ll show.
In the most straightforward case you may have a list of numbers in column A and you need to order them, to do this the function “=countif(A:A,”<“&,A2)+1” would provide how many numbers are less than A1, you could carry the function on and get a list that looks something like this example below:
Excel lacks a rank function, but one can be built using countif. The screenshot shows a simple rank done using “=COUNTIF(A:A,”<=”&A2)”. This will cause some issues if the data contains duplicate values, but works well for unique data sets.
In a more complex situation numbers may countifs allows for rankings to incorporate a group that needs to be ranked. In the example below it ranks based on group in column A and numbers in column B need to be ranked the formula looks like “=COUNTIFS(B:B,”<“&B2,A:A,A2)+1”. This allows for checking that an item belongs to the correct group and then ranking them. So for instance if column A is a region and B is sales, each member of the region is ranked only against it’s own region.
Ranking with countif can have it’s problems if you aren’t carefull. Issues such as duplicate numbers may lead to multiple items of the same rank. This may or may not be desirable, but if it’s not intended there are still workaround. With countifs you can rank over multiple columns, or just rank by order items appear on the sheet with a formula such as “=COUNTIF(A:A,”<“&A2)+COUNTIF($A$1:A1,A2)+1” This method looks for items less than the one in cell A2, then goes on too look for items in the sheet equal to A2 which occurred previously. This prevents duplicate ranks. There are many more options for various use cases, but I think this provides a better solution than many I have seen.