Excel: Ranking with another Criteria

[BlazFilip]

	A	B	C	D
1	Name	Bonus	Years of Work	Rank Per Bonus
2	John	50	35
3	Mary	50	15
4	Tommy	60	20
5	Jack	50	16
6	George	70	16

<tbody>
</tbody>

So I know how to rank normally, in D2 I would type =RANK(B2;$B$2:$B$6;0) and then just drag it down how much I need, but here I want the following: If the bonus is the same, then use Years of work to rank the ones where the bonus is the same, basically getting the following:

Rank Per Bonus

3

5

2

4

1

<tbody>
</tbody>

Is there any way to do this using the =RANK function + some others?
Thanks in advance!

 

[barry houdini]

Hello BlazFilip, welcome to MrExcel

If you are using Excel 2007 or later you can add a COUNTIFS function to RANK like this:

=RANK(B2;$B$2:$B$6;0)+COUNTIFS($B$2:$B$6;$B2;$C$2:$C$6;">"&$C2)