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

[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

 

[T. Valko]

Try this:

=SUMPRODUCT(--($B$2:$B$11=B2),--(C2<C$2:C$11))+1

Copy down as needed

 

[russianin]

Thanks, that worked great.

 

[Ron Rosenfeld]

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

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

 

[T. Valko]

You're welcome!