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.


[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Division[/TD]
[TD="class: xl63, width: 64"]Area[/TD]
[TD="class: xl63, width: 64"]Sales[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]Rank 66 & 67.[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]90[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]50[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]70[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TM[/TD]
[TD="align: right"]67[/TD]
[TD="align: right"]60[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]TS[/TD]
[TD="align: right"]66[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Maybe:

	A	B	C	D	E	F
Division	Area	Sales	Rank 66 & 67.
TS
TS
TS
TS
TM
TS
TM
TM
TM
TS

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]66[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]66[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]67[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]68[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]66[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]67[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]66[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]68[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]67[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]66[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

</tbody>

Sheet13​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]{=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)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



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.)