Rank with multiple creteria

[SohailC]

Hi,

I want to rank score base on division value. I have used below formula in cell C2 but not getting actual value as shown in D column. Any ideal please

=IF(A2="A",SUMPRODUCT(--(COUNTIF(OFFSET($B$2,,,ROW($B$2:$B$9)-ROW($B$2)+1),$B$2:$B$9)=1),--($B$2:$B$9>=B2),--($A$2:$A$9="A")),IF(A2="B",SUMPRODUCT(--(COUNTIF(OFFSET($B$2,,,ROW($B$2:$B$9)-ROW($B$2)+1),$B$2:$B$9)=1),--($B$2:$B$9>=B2),--($A$2:$A$9="B")),""))


Untitled.jpg

[alfiano]

Hi,

you can use Rank Function
put on column C2 and paste until C5; =RANK(B2,$B$2:$B$5),
Put on column C6and paste until C9; =RANK(B6,$B$6:$B$9)

[martindwilson]

hmm closest i can get at the moment is
=COUNTIFS($A$2:$A$9,A2,B$2:B$9,">"&B2)+1
gives
a 3 4
a 44 1
a 6 2
a 6 2
b 34 2
b 1 4
b 44 1
b 3 3

[SohailC]

Thanks Martin, but not getting exact result as I want. First row result showing 4 instead of 3. Formula is not giving proper result when there is duplicate value in division A & B. Anyone has idea to get proper result as shown below in column D

Untitled.jpg

[SohailC]

Untitled.jpgThanks Martin, but not getting exact result as I want. First row result showing 4 instead of 3. Formula is not giving proper result when there is duplicate value in division A & B. Anyone has idea to get proper result as shown below in column D

Attachment 354524

[martindwilson]

try this array entered formula
=SUM(IF(FREQUENCY(IF(A$2:A$19=A2,IF(B$2:B$19>B2,B$2:B$9)),B$2:B$9),1))+1

[SohailC]

Thanks Martin, this formula is working excellent 100/100. One again thanks a lot