Ranking Based on Multiple Criteria in Excel (4 Cases)

Often you may need to rank items from a dataset based on multiple criteria. More specifically, you have to accomplish this task when there are ties in one column. In this instructive session, I’ll demonstrate 4 cases with the proper explanation of ranking in Excel based on multiple criteria.

Ranking in Excel Based on Multiple Criteria: 4 Practical Cases

Let’s introduce today’s dataset where Scores of the Student in Math and Psychology are given according to their corresponding Group. Here, D6 and D7 cells are tied in column D. So, let’s apply the ranking with consideration of column E.


1. Using RANK.EQ and COUNTIFS Functions

In the beginning method, I’ll show you the combined use of the RANK.EQ function and the COUNTIFS function. To rank based on the two Scores, insert the following formula.


Here, C5 and D5 are the starting cell of Score (Math) i.e. column C, and Score (Psychology) i.e. column D respectively.

Formula Explanation:

  • The RANK.EQ function returns the rank number from the C5:C15 cell range based on the C5 cell. Unfortunately, it provides the same rank for the duplicate scores (e.g. rank number is 7 for C6, C7, and C12 cells).
  • So, the COUNTIFS function is assigned in descending order (“>”&$D5) to count duplicate scores. For example, the function returns 1 for the C7 cell and 2 for the C12 cell.
  • However, when you sum the two outputs i.e. the output RANK.EQ function and the output of the COUNTIFS function, you’ll get the unique rank number for all students.

Using RANK.EQ and COUNTIFS Functions

After pressing ENTER and using the Fill Handle tool, you’ll get the following output.

Ranking in Excel Based on Multiple Criteria Using RANK.EQ and COUNTIFS Functions

If you look closely at the above image, you’ll get that the Rank for Robert Smith is 7 (look at the B6:E6 cells) whereas it is 8 for Jim Brown (look at the B7:E7 cells).

2. Ranking Based on Multiple Criteria Using COUNTIF and COUNTIFS Functions

Similarly, you may use the COUNTIF function instead of the RANK.EQ function.


Here, I want to rank the scores in ascending order (“<“&$D5).

Formula Explanation:

  • The COUNTIF function counts the number of cells having values greater than the corresponding cell (like C5 for James Smith, C6 for Jim Brown, and so on).
  • Lastly, you have to add 1 with the output as the COUNTIF function returns 0 for the smallest values i.e. for the C13 cell.

Using COUNTIF and COUNTIFS Functions

So, the output will be as follows.

Ranking in Excel Based on Multiple Criteria Using COUNTIF and COUNTIFS Functions

3. Applying RANK and SUMPRODUCT Functions 

Also, you can utilize both the RANK function and SUMPRODUCT function for ranking the items based on multiple criteria.

Now, look at the following dataset from where you need to rank based on the GRE Score (Quant) and Financial Aid. But the cell values of C10 and C11 are tied.

Applying RANK and SUMPRODUCT Functions

So, insert the following combined formula.


Formula Explanation:

  • The RANK function returns the rank number from the $C$5:$C$15 cell range based on the C5 cell with the duplicates value in the C10 and C11 cells (the rank number is 2).
  • And, the SUMPRODUCT function finds 0 in case of no tied values. But it returns 1 for the C10 cell.
  • Notably, the () operator is used to return 1 instead of getting TRUE and 0 for FALSE.
  • Thus, you can easily avoid the duplicate rank number using this formula.

Applying RANK and SUMPRODUCT Functions

Finally, the output will look as follows.

Ranking in Excel Based on Multiple Criteria Applying RANK and SUMPRODUCT Functions

Instead of utilizing the RANK function, you can use the COUNTIF function. But you have to add 1 in that case.


Applying COUNTIF and SUMPRODUCT Functions

Certainly, you’ll get the same output.

Ranking in Excel Based on Multiple Criteria Applying COUNTIF and SUMPRODUCT Functions

4. Ranking with Multiple Criteria by Group

What if you have some common Groups in your dataset? For example, the Science group covers C5:C6 and C11:C12 cells.

Ranking in Excel with Multiple Criteria by Group

Fortunately, you can get the unique rank number dealing with both Group and Score. We have functions that can help us rank in Excel based on multiple criteria on groups.

4.1. Using COUNTIFS Function

Using the COUNTIFS function, you can easily rank the Score by the assigned Group in descending order (“>”&D5).


Formula Explanation:

  • The COUNTIFS($C$5:$C$15,C5) returns 4 as there are 4 strings available namely Science.
  • And, the COUNTIFS($C$5:$C$15,C5,$D$5:$D$15,”>”&D5) syntax returns 0 for the highest scores (e.g. for the E6 cell). That’s why you need to add 1.

Ranking in Excel Based on Multiple Criteria by Group Using COUNTIFS Funciton

Here, the scores are ranked based on the Group separately. For example, Jim Brown (B6 cell) is ranked 1st though the score of Mary Smith (B13 cell) is greeted than him.

Read More: How to Rank Within Group in Excel

4.2. Using SUMPRODUCT Function

Similarly, you can use the following formula where the SUMPRODUCT function is used (ranking in ascending order).


Formula Explanation:

  • The SUMPRODUCT((C5=$C$5:$C$15) returns 0.
  • Besides, the SUMPRODUCT((C5=$C$5:$C$15)*($D5<$D$5:$D$15)) finds 2. But the SUMPRODUCT function returns for E7 cell is 0 as it is the smallest score. So, you need to 1 to avoid such type of error.

Ranking in Excel Based on Multiple Criteria by Group Using SUMPRODUCT Function

Download Practice Workbook


That’s the end of today’s session. This is how you may accomplish raking in Excel based on multiple criteria. Anyway, if you have any queries or recommendations, please share them in the comments section.

Related Articles

<< Go Back to Excel RANK Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

  1. I need your support to find a formula for aging stock to put a value to a range of time based on the remaining quantity and aging from invoiced quantity

  2. Hi NGÂN,
    The solution you want will require a combination of some functions like TODAY, COUNTIF, VLOOKUP, etc. Here is a post on our website that will help you.
    We have several posts related to this topic too.
    I hope these articles will help get your job done. If not, please remember that we are just a text away!!
    Thank you. Have a good day.

  3. Thanks for providing the guide. How do you rank without duplicates in the case of 4. Ranking with Multiple Criteria by Group? So instead of having duplicate ranks, I want to avoid them without skipping any number. Thanks in advance

    • Reply Avatar photo
      Rubayed Razib Suprov May 28, 2023 at 5:12 PM

      Greetings Edward,
      Thanks a lot for your Question in our blog post. Now the issue you have is a little bit unclear to me. Can you provide a sample output manually which will contain your desired result? In that way, your problem be more clear to us and in turn it will help us to resolve your problem.

  4. How would you rank with multiple criteria and duplicates? In the initial example above, you see Jim Brown and Henry James with Science and 65 scores. How would you rank and not repeat numbers?

    Thank you.

    • Reply Mahfuza Anika Era
      Mahfuza Anika Era Feb 29, 2024 at 11:51 AM

      Thank you for your comment. In the initial example, Jim Brown, Robert Smith, and Henry James scored 65 in Science. So if you rank them according to the score of Science, you will get repeated ranks. But, as you can see, these 3 students got different scores in Psychology. So, we have ranked them according to the E column (Psychology). Hence, the rank is not repeated, and they got different ranks according to Psychology score.

      Mahfuza Anika Era

  5. I am looking for a ranking formula that will rank salesmen by region based on number of units sold and then (in the case of ties) by total sales amount. I need unique rankings so there are no duplicates. Any help you can provide is greatly appreciated. I have tried this so many ways and never getting the desired outcome.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Mar 24, 2024 at 12:34 PM

      Hello Denise Sano

      Thanks for visiting our blog and leaving an exciting comment. You want to rank salespeople by region based on number of units sold and then (in the case of ties) by total sales amount. However, developing such a formula using Excel’s built-in function would be time-consuming.

      So, I have developed an Excel VBA sub-procedure to help you overcome your situation.

      Follow these steps:

      1. Press Alt+F11.
      2. Click on Insert followed by Module.
      3. Paste the following code into the Module and run it:
        Sub RankSalesRepByRegion()
            Dim ws As Worksheet
            Dim lastRow As Long
            Dim salesRepCol As Range, regionCol As Range, unitsSoldCol As Range, salesAmountCol As Range, rankCol As Range
            Dim region As Range, uniqueRegions As New Collection
            Dim i As Long
            Set ws = ThisWorkbook.Sheets("Sheet1")
            lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
            Set salesRepCol = ws.Range("A2:A" & lastRow)
            Set regionCol = ws.Range("B2:B" & lastRow)
            Set unitsSoldCol = ws.Range("C2:C" & lastRow)
            Set salesAmountCol = ws.Range("D2:D" & lastRow)
            Set rankCol = ws.Range("E2:E" & lastRow)
            For Each region In regionCol
                On Error Resume Next
                uniqueRegions.Add region.Value, CStr(region.Value)
                On Error GoTo 0
            Next region
            For i = 1 To uniqueRegions.Count
                Dim regionName As String
                regionName = uniqueRegions(i)
                Dim salesReps() As String
                Dim unitsSold() As Long
                Dim salesAmount() As Double
                Dim ranks() As Integer
                Dim j As Long
                Dim uniqueCount As Long
                uniqueCount = 0
                For j = 1 To lastRow - 1
                    If regionCol.Cells(j, 1).Value = regionName Then
                        uniqueCount = uniqueCount + 1
                        ReDim Preserve salesReps(1 To uniqueCount)
                        ReDim Preserve unitsSold(1 To uniqueCount)
                        ReDim Preserve salesAmount(1 To uniqueCount)
                        salesReps(uniqueCount) = salesRepCol.Cells(j, 1).Value
                        unitsSold(uniqueCount) = unitsSoldCol.Cells(j, 1).Value
                        salesAmount(uniqueCount) = salesAmountCol.Cells(j, 1).Value
                    End If
                Next j
                For j = 1 To uniqueCount - 1
                    For k = j + 1 To uniqueCount
                        If unitsSold(j) < unitsSold(k) Or (unitsSold(j) = unitsSold(k) And salesAmount(j) < salesAmount(k)) Then
                            Dim tempSalesRep As String
                            tempSalesRep = salesReps(j)
                            salesReps(j) = salesReps(k)
                            salesReps(k) = tempSalesRep
                            Dim tempUnitsSold As Long
                            tempUnitsSold = unitsSold(j)
                            unitsSold(j) = unitsSold(k)
                            unitsSold(k) = tempUnitsSold
                            Dim tempSalesAmount As Double
                            tempSalesAmount = salesAmount(j)
                            salesAmount(j) = salesAmount(k)
                            salesAmount(k) = tempSalesAmount
                        End If
                    Next k
                Next j
                ReDim ranks(1 To uniqueCount)
                ranks(1) = 1
                For j = 2 To uniqueCount
                    If unitsSold(j - 1) = unitsSold(j) And salesAmount(j - 1) = salesAmount(j) Then
                        ranks(j) = ranks(j - 1)
                        ranks(j) = j
                    End If
                Next j
                For j = 1 To uniqueCount
                    For k = 1 To lastRow
                        If salesRepCol.Cells(k, 1).Value = salesReps(j) Then
                            rankCol.Cells(k, 1).Value = ranks(j)
                            Exit For
                        End If
                    Next k
                Next j
            Next i
        End Sub

      As a result, you get the intended rank like the following GIF.

      I have attached the solution workbook for better understanding. Hopefully, the idea will help; good luck.



      Lutfor Rahman Shimanto

      Excel & VBA Developer


Leave a reply

Advanced Excel Exercises with Solutions PDF