How to Rank within Groups in Excel

If you have a list of numbers, it easy to rank it using the RANK function. However, if you have these numbers within various groups, it could be a challenge to find the rank within the groups.

For example, as shown below, there are two groups (A and B) with 5 items in each group. Each item has a score in column C. Now there are two rankings done with this data set. Column D has the overall rankings and Column E has the group-wise rankings.

Now there are two rankings done with this data set. Column D has the overall rankings and Column E has the group-wise rankings.

Rank within Groups in Excel - Data Set

While the overall ranking can easily be done using the RANK function, the one for groups is a bit more complex.

Rank within Groups in Excel

Here is the formula that will give the ranks within groups in Excel:

=SUMPRODUCT((A2=$A$2:$A$11)*(C2<$C$2:$C$11))+1

How does this work?

This formula checks for 2 conditions:

  • (A2=$A$2:$A$11) – This part returns an array of TRUEs/FALSEs based on the group. So if you use this in cell E2, it will check A2:A11 and return TRUE wherever it finds Group A and FALSE when it finds any other group.
  • (C2<$C$2:$C$11) – This checks whether the score is less than the other scores in C2:C11, and returns TRUE if it’s less and FALSE if it’s more.

When these two arrays of TRUEs/FALSEs are multiplied, it will return TRUE only in those cases where both the conditions are met, i.e., the group matches and the scores are less than the score in the row where the formula is used.

The SUMPRODUCT formula then simply returns the total count of such instances where the conditions are met.

1 is added to the SUMPRODUCT result to get the rank of the given score within that group.

Click here to download the example file.

I hope you found this tutorial useful!

You May Also Like the Following Excel Tutorials:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Picture of Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

8 thoughts on “How to Rank within Groups in Excel”

  1. If I only want to rank Group A and not Group B. How would that be achieved by either include certain values or excluding certain values?

    Reply
  2. We can also use COUNTIFS for ranking in Groups. Formula is
    =COUNTIFS($C$2:$C$11,”>=”&C2,$A$2:$A$11,A2)
    entering the formula as array formula

    Reply
  3. {=SUM((A2=$A$2:$A$11)*(C2<=$C$2:$C$11))} gives the same result (typed without the {}'s and entered in the cell with CTRL-SHIFT-ENTER).
    Both are able to be expanded as long as data is inserted in the middle or change $11 to $12, in the example formula and insert rows at the last cell of the array before entering new data. The benefit of the C-S-E Array is it has some protection from editing built in.

    By the way, above you say the formula ends with "+1", but in the example file, it is not there.

    Reply
    • There is another difference in the formula that makes the formula above superior to the formula in the downloaded example – the “<" above is replaced with "<=" in the example – as noted in +1's comment.
      For example, a data set with ties using the formula above would result in "1,1,3,4,4,6".
      The same data set with the formula in the download would result in "2,2,3,5,5,6".

      Reply
    • Hey Jim.. Thanks for pointing out.. I first used the formula with =< without +1, but later found the issue so changed in the tutorial. Forgot to change in the download file. It's fixed now 🙂

      Reply
  4. You can change the “<" to "=<" (equal or less) in the second multiplier and can then get rid of the "+1."
    This will get you the same effect when the items to be ranked are mutually unequal.
    When breaking ties is needed, the second formula will get you the upper bound for the rank, which is somewhat unsatisfactory. Perhaps this is what the design with "+1" was chosen in the first place. Too late to delete this comment, though.

    Reply

Leave a Comment

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free-Excel-Tips-EBook-Sumit-Bansal-1.png

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Free Excel Tips EBook Sumit Bansal

FREE EXCEL E-BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster