Rank Multiple Criteria Duplicates

[Lewzerrrr]

Hi,

I have a table of data that looks like below.

I need to be able to rank seperately both the TS & TM division sales whilst only looking at certain criteria, in this case 66 and 67 but these numbers just a small sample.

My actual worksheet is a dump with over 30 different Area's.

Division	Area	Sales		Rank 66 & 67.
TS	66	50		2
TS	66	50		3
TS	67	1		5
TS	68	90
TM	66	80		1
TS	67	50		4
TM	66	60		2
TM	68	70
TM	67	60		3
TS	66	100		1

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Eric W]

Maybe:

	A	B	C	D	E	F
1	Division	Area	Sales		Rank 66 & 67.
2	TS	66	50		2	2
3	TS	66	50		2	3
4	TS	67	1		5	5
5	TS	68	90
6	TM	66	80		1	1
7	TS	67	50		2	4
8	TM	66	60		2	2
9	TM	68	70
10	TM	67	60	2	3
11	TS	66	100		1	1

<tbody>
</tbody>

Sheet13​

Worksheet Formulas Cell Formula E2 =IF(AND(B2<>66,B2<>67),"",SUM(COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,{66,67},$C$2:$C$11,">"&$C$2:$C$11))+1) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula F2 {=IF(AND(B2<>66,B2<>67),"",SUM(IF($A$2:$A$11=A2,IF(ISNUMBER(MATCH($B$2:$B$11,{66,67},0)),IF(C2-ROW()/1000<$C$2:$C$11-ROW($C$2:$C$11)/1000,1))))+1)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The E2 formula gives the same ranking to Sales with the same amount. If you want to break the ties, the F2 array formula will use the row number as a tie-breaker, and matches your desired output. (Small formatting issue on line 10.)