excelexcel-formulaexcel-2013rankingrank

How to Rank Items by Two (Three) Criteria in Excel

1) Copy the following values and paste into A1:E9 cells in excel sheet.

Player Points Assists Rank column B firstly then column C secondly Unique Rank for column B only
Andy 10 8 =RANK.EQ($B2,$B$2:$B$9)+COUNTIFS($B$2:$B$9,$B2,$C$2:$C$9,">"&$C2) =RANK.EQ($B2,$B$2:$B$9)+COUNTIFS($B$2:B2,B2)-1
Bernard 10 2 =RANK.EQ($B3,$B$2:$B$9)+COUNTIFS($B$2:$B$9,$B3,$C$2:$C$9,">"&$C3) =RANK.EQ($B3,$B$2:$B$9)+COUNTIFS($B$2:B3,B3)-1
Carl 6 4 =RANK.EQ($B4,$B$2:$B$9)+COUNTIFS($B$2:$B$9,$B4,$C$2:$C$9,">"&$C4) =RANK.EQ($B4,$B$2:$B$9)+COUNTIFS($B$2:B4,B4)-1
Derrick 6 3 =RANK.EQ($B5,$B$2:$B$9)+COUNTIFS($B$2:$B$9,$B5,$C$2:$C$9,">"&$C5) =RANK.EQ($B5,$B$2:$B$9)+COUNTIFS($B$2:B5,B5)-1
Erin 8 6 =RANK.EQ($B6,$B$2:$B$9)+COUNTIFS($B$2:$B$9,$B6,$C$2:$C$9,">"&$C6) =RANK.EQ($B6,$B$2:$B$9)+COUNTIFS($B$2:B6,B6)-1
Frank 8 6 =RANK.EQ($B7,$B$2:$B$9)+COUNTIFS($B$2:$B$9,$B7;$C$2:$C$9,">"&$C7) =RANK.EQ($B7,$B$2:$B$9)+COUNTIFS($B$2:B7,B7)-1
Greg 3 6 =RANK.EQ($B8,$B$2:$B$9)+COUNTIFS($B$2:$B$9,$B8,$C$2:$C$9,">"&$C8) =RANK.EQ($B8,$B$2:$B$9)+COUNTIFS($B$2:B8,B8)-1
Harry 2 9 =RANK.EQ($B9,$B$2:$B$9)+COUNTIFS($B$2:$B$9,$B9,$C$2:$C$9,">"&$C9) =RANK.EQ($B9,$B$2:$B$9)+COUNTIFS($B$2:B9,B9)-1

2) Please look at the following picture.

This is the picture.

3) Explanation of my problem is here:

Formula in the D column ranks column B firstly then column C secondly.

Formula in the D column ranks duplicates.

Number 4 doesnt exist in the D column because values for Erin and Frank are equal.

Formula in the E column ranks only B column.

Formula in the E column doesnt rank duplicates.

Number 4 exists in the E column because the formula in the E column doesnt rank duplicates.

I prefer to use formula in the E column because formula in the E column doesnt rank duplicates.

4) My question is here:

Please improve formula in the E column that supposed to be rank column B firstly then column C secondly.

Solution

  • The formula is in cell D25

    =RANK.EQ($B25,$B$25:$B$32)+COUNTIFS($B$25:$B$32,$B25,$C$25:$C$32,">"&$C25)+COUNTIFS(B$24:B24,B25,C$24:C24,C25)

    Since it takes one row above the table, the table cannot be started in row 1. If there is a header, then it works and return this result:

    enter image description here