Rank With Multiple Crieteria

[xlmaniac]

Dear All,
I have a data set spread across thousands of row.
Below is the sample for the same.
I would like to calculate rank based on the Region & Category.
The sample data set is spread across A1:D7.

Region	Category	Sale	Rank
A	Z	100	3
A	Z	140	1
A	Z	105	2
B	C	170	3
B	C	215	2
B	C	220	1 Could Somebody help me with the formulae across D2:D7 which can yield the desired result?

<tbody>
</tbody>

 

[Domenic]

Try...

D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=MATCH(C2,LARGE(IF($A$2:$A$7=A2,IF($B$2:$B$7=B2,$C$2:$C$7)),ROW(INDIRECT("1:"&COUNTIFS($A$2:$A$7,A2,$B$2:$B$7,B2)))),0)

Hope this helps!