Ranking based on Two coloumns

[lalro143]

Hi,

I have two columns as mentioned below col A & col B.

Col A	Col B	Col C
SITES	COMPLIANCE	RANK
3000	90%	2
4000	90%	1

<colgroup><col width="64" style="width: 48pt;"> <col width="89" style="width: 67pt; mso-width-source: userset; mso-width-alt: 3254;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

Now I want formula which can determine ranking based first on Col B, In case % of cells in col B are same then it check col A's value and rank based site count as shown above.

Thanks in advance.

 

[steve the fish]

Try this in C2, useful up to row 50 or you will need to amend.

=RANK(B2,$B$2:$B$50)+SUMPRODUCT(($B$2:$B$50=B2)*($A$2:$A$50>A2))

 

[Oeldere]

SITES COMPLIANCE RANK helpcolumn rank
3000 90% 2 0,93000 2
2000 89% 2 0,89200 3
1400 89% 2 0,89140 4
4000 90% 1 0,94000 1


d2= =(B2&A2)*1 and drag down

e2 = =RANK($D2,$D$2:$D$5) and drag down

the result is showing above.