Excel formula help (Need to do a rank based on 2 criteria)

  • Thread starter Thread starter russianin
  • Start date Start date
R

russianin

I need to be able to return a rank of something based on 2 criteria.
An example of this is below. I need to get the rank of Mike within
his team. In this case, Joe is on Team 1 and has the lowest score
among the group, so his rank is 5. John is rank 3 (3rd on Team 2).
Can anyone please help me write this formula. Thanks!

A B C
1 Name Team # Score
2 Joe Team 1 1
3 John Team 2 9
4 Jack Team 1 3
5 Bill Team 2 16
6 Tom Team 1 7
7 Sarah Team 2 4
8 Mike Team 1 20
9 Maria Team 2 5
10 Laura Team 1 14
11 Bob Team 2 12
 
I need to be able to return a rank of something based on 2 criteria.
An example of this is below. I need to get the rank of Mike within
his team. In this case, Joe is on Team 1 and has the lowest score
among the group, so his rank is 5. John is rank 3 (3rd on Team 2).
Can anyone please help me write this formula. Thanks!

A B C
1 Name Team # Score
2 Joe Team 1 1
3 John Team 2 9
4 Jack Team 1 3
5 Bill Team 2 16
6 Tom Team 1 7
7 Sarah Team 2 4
8 Mike Team 1 20
9 Maria Team 2 5
10 Laura Team 1 14
11 Bob Team 2 12
Click to expand...

If you NAME your columns, Team & Score, enter this array formula and fill down
for the number of entries.

This is an **ARRAY** formula so must be entered by holding down <ctrl><shift>
while hitting <enter>.


=COUNTIF(Team,B2)+1-MATCH(TRUE,C2=LARGE(Score*(B2=Team),
ROW(INDIRECT("1:"&COUNTIF(Team,B2)))),0)
--ron
 
Back
Top