6 Replies to “How to Rank Items by Multiple Criteria in Excel”

1. Hello, The formula – =RANK.EQ($B2, $B$2:$B$9, 1) + COUNTIFS($B$2:$B$9, $B2, $C$2:$C$9, “<" &$C2) has really helped me out but is there a way to get a third criteria. I have tried to work it out but havent been able to.

   Reply

   1. You just add another combination of column/array and the corresponding cell to refer to.
      I did this with “games won”, “sets won”, “points won”
      It’s in German but I am sure, you can figure it out:
      =RANG.GLEICH($AK7;$AK$7:$AK$16;0)+ZÄHLENWENNS($AK$7:$AK$16;$AK7;$AN$7:$AN$16;$AN7;$AQ$7:$AQ$16;”>”&$AQ7)

      Reply

2. Hey, how would I rank with a second set of criteria? Im trying to rank a numeric value if x=y, and also a=b

   Reply

   1. Hi Ella…To rank items in Excel by multiple criteria where you want to rank a numeric value only if two conditions (e.g., \(x = y\) and \(a = b\)) are met, you can use a combination of the `RANK` or `RANK.EQ` function with `IF` and `AND` functions. Here’s how you can do it:

      ### Example Scenario
      Let’s assume you have the following columns in your Excel sheet:
      – **Column A**: Values for \( x \)
      – **Column B**: Values for \( y \)
      – **Column C**: Values for \( a \)
      – **Column D**: Values for \( b \)
      – **Column E**: Numeric values you want to rank (e.g., sales figures)
      – **Column F**: Criteria 1 (e.g., \( x = y \))
      – **Column G**: Criteria 2 (e.g., \( a = b \))
      – **Column H**: The column where you want to display the rank based on the conditions.

      ### Steps to Rank with Multiple Criteria

      1. **Create the Criteria**: First, ensure you have columns where the criteria are evaluated.
      – In **Column F**: Enter the formula `=A2=B2` to check if \( x = y \).
      – In **Column G**: Enter the formula `=C2=D2` to check if \( a = b \).

      2. **Rank the Numeric Value**: Use the `RANK.EQ` function in combination with `IF` and `AND` to rank the numeric values based on the criteria.

      – In **Column H** (e.g., cell H2), enter the following formula:

      “`excel
      =IF(AND(F2=TRUE, G2=TRUE), RANK.EQ(E2, $E$2:$E$10), “”)
      “`

      – Here’s what this does:
      – `AND(F2=TRUE, G2=TRUE)`: Checks if both conditions \( x = y \) and \( a = b \) are true.
      – `RANK.EQ(E2, $E$2:$E$10)`: Ranks the value in column E (numeric value) relative to all other values in column E, but only if the criteria are met.
      – If the criteria aren’t met, it returns a blank value `””`.

      ### Explanation
      – **Column F and G** are helping you identify rows where the conditions are true.
      – **Column H** gives you the rank of the numeric value in Column E, but only for the rows where both conditions are satisfied.

      ### Additional Tips
      – If you want to handle ties differently, you can use the `RANK.AVG` function instead of `RANK.EQ`.
      – If you need to rank in ascending order, you can add a third argument to `RANK.EQ`, like so: `RANK.EQ(E2, $E$2:$E$10, 1)`.

      This approach will give you a ranking that considers the specified criteria. If a row doesn’t meet the criteria, it won’t be ranked, and the cell in Column H will remain blank.

      Reply

3. Hi there, any one who can help me, I have a data of four criteria (each have a 25% share) and I want to score it based on (if four of have >50% which means 12.5, I want to give 1 as a core value, if 3 of have >50% and 1 have <50%, I want to give 2 as a score value, …)
   A B C D Score
   16 16 12 13
   25 16 9 8
   16 7 10 9
   25 10 12 9

   Reply

   1. Hi Tewachew…To rank your data in Excel based on the four criteria, each having a 25% weight and scoring according to your rules (1, 2, etc.), follow this structured solution.

      ### Steps to Implement the Ranking Logic:

      1. **Create a Helper Column to Track Values > 50%**:
      – Use a formula to count how many of the four criteria (A, B, C, D) exceed 50% of their range.
      – Let’s assume your data starts in **columns A to D**, with the header in row 1 and values starting from row 2.

      2. **Formula to Count Criteria Greater Than 50%**:
      In **column E (Helper)**, write the following formula for row 2:

      “`excel
      =SUM(IF(A2>12.5, 1, 0), IF(B2>12.5, 1, 0), IF(C2>12.5, 1, 0), IF(D2>12.5, 1, 0))
      “`

      This formula will count how many of the four criteria exceed the threshold (50% of 25 is 12.5). Adjust the threshold if needed.

      **Drag the formula down** for all the rows in your dataset.

      3. **Assign Scores Based on the Criteria Met**:
      Now in **column F (Score)**, use this formula:

      “`excel
      =IF(E2=4, 1, IF(E2=3, 2, IF(E2=2, 3, IF(E2=1, 4, 5))))
      “`

      – This assigns:
      – 1 if all four values are >12.5
      – 2 if three values are >12.5
      – 3 if two values are >12.5
      – 4 if one value is >12.5
      – 5 if none of the values are >12.5

      4. **Format and Review**:
      – Drag the formula in **column F** down to apply it to all the rows.
      – You should now see the score based on your specified logic.

      —

      ### Example Output:

      | **A** | **B** | **C** | **D** | **Helper (E)** | **Score (F)** |
      |——-|——-|——-|——-|—————-|—————|
      | 16 | 16 | 12 | 13 | 4 | 1 |
      | 25 | 16 | 9 | 8 | 2 | 3 |
      | 16 | 7 | 10 | 9 | 1 | 4 |
      | 25 | 10 | 12 | 9 | 2 | 3 |

      This setup ensures your scores are assigned according to your specified rules.

      Reply