Home » Excel Formulas » Ranking Based on Multiple Criteria in Excel (4 Cases)

Ranking Based on Multiple Criteria in Excel (4 Cases)

Avatar photo
Written by Md. Abdul Kader
Last updated: Jun 25, 2024

The sample dataset contains student scores in Math and Psychology as well as each student’s group. We’ll rank the students according to the scores.

Dataset

Method 1 – Using RANK.EQ and COUNTIFS Functions

  • Use the following formula:

=RANK.EQ($C5,$C$5:$C$15)+COUNTIFS($C$5:$C$15,$C5,$D$5:$D$15,">"&$D5)

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

Using RANK.EQ and COUNTIFS Functions

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

Method 2 – Ranking Based on Multiple Criteria Using COUNTIF and COUNTIFS Functions

  • Use the following formula:

=COUNTIF($C$5:$C$15,"<"&$C5)+COUNTIFS($C$5:$C$15,$C5,$D$5:$D$15,"<"&$D5)+1

We want to rank the scores in ascending order (“<“&$D5). The COUNTIF function counts the number of cells in column D with values greater than the corresponding cell (like C5 for James Smith, C6 for Jim Brown, and so on). We then add 1 to make sure the rankings start from 1.

Using COUNTIF and COUNTIFS Functions

  • Here’s the output.

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

Method 3 – Applying RANK and SUMPRODUCT Functions 

In the following dataset, we’ll rank based on the GRE Score (Quant) and Financial Aid.

Applying RANK and SUMPRODUCT Functions

  • Insert the following formula in E5 and AutoFill through the column:

=RANK(C5,$C$5:$C$15)+SUMPRODUCT(--($C$5:$C$15=$C5),--(D5<$D$5:$D$15))

⧬ 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).
  • The SUMPRODUCT function finds 0 in case of no tied values. But it returns 1 for the C10 cell.
  • The (—) operator is used to return 1 instead of getting TRUE and 0 for FALSE.

Applying RANK and SUMPRODUCT Functions

Here are the results.

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

  • Here’s an alternative formula you can use:

=COUNTIF($C$5:$C$15,">"&$C5)+SUMPRODUCT(--($C$5:$C$15=$C5),--(D5<$D$5:$D$15))+1

Applying COUNTIF and SUMPRODUCT Functions

You’ll get the same output.

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

Method 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

Case 4.1 – Using the COUNTIFS Function

  • Use the following formula and AutoFill through the column.

=COUNTIFS($C$5:$C$15,C5,$D$5:$D$15,">"&D5)+1

⧬ Formula Explanation:

  • COUNTIFS($C$5:$C$15,C5) returns 4 as there are 4 cells that contain Science (C5).
  • 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).
  • We’re adding 1 to all rankings.

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

Read More: How to Rank Within Group in Excel

Case 4.2 – Using SUMPRODUCT Function

  • Use the following formula:

=SUMPRODUCT((C5=$C$5:$C$15)*($D5<$D$5:$D$15))+1

⧬ Formula Explanation:

  • SUMPRODUCT((C5=$C$5:$C$15) returns 0.
  • SUMPRODUCT((C5=$C$5:$C$15)*($D5<$D$5:$D$15)) finds 2. But the SUMPRODUCT function returns 0 for E7 cell as it is the smallest score. We added 1 to make a more readable list.
  • The formula scores in ascending order, giving 1 to the lowest-scoring student within their group.

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

Download the Practice Workbook

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!

Tags:

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

8 Comments
  1. Reply
    ngân Nov 11, 2022 at 9:58 PM

    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. Reply Avatar photo
    Akib Nov 13, 2022 at 11:22 AM

    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.
    https://www.exceldemy.com/stock-ageing-analysis-formula-in-excel/
    We have several posts related to this topic too.
    https://www.exceldemy.com/make-inventory-aging-report-in-excel/
    https://www.exceldemy.com/excel-ageing-formula-for-30-60-90-days/
    https://www.exceldemy.com/aging-of-accounts-receivable-in-excel/
    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. Reply
    Edward May 28, 2023 at 12:50 AM

    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. Reply
    Guillermo Alcala Feb 29, 2024 at 1:31 AM

    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

      Hello GUILLERMO ALCALA,
      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.

      Regards
      Mahfuza Anika Era
      Exceldemy

  5. Reply
    Denise Sano Mar 22, 2024 at 7:40 AM

    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)
            Else
                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.

      DOWNLOAD SOLUTION WORKBOOK

      Regards

      Lutfor Rahman Shimanto

      Excel & VBA Developer

      ExcelDemy

Leave a reply

About ExcelDemy.com

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

See Our Reviews at

trustpilot review

ExcelDemy Consulting Services

Submit Excel Task

ExcelDemy Online Training


Join ExcelDemy Forum
DMCA.com Protection Status

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo