SOLVED

Rank number within Group in Excel

New Contributor

Hello everyone, I have this dataset and I want to rank peoples score in each Group, but I want to same score have same rank, also I dont want to skip number. finally I want Somthing like "Rank" column.

Thank You   @cuong 

GroupPersonScoreRank(What I want)
APeter.J122
AYoung113
AMj.lan24
ADidan.v24
APandas35
ALanda331
BJack34
BNumes123
BPeter jackson152
BJohn123
BAnne181
CChannel122
CAngelina201
CSijal201
CDavid122
CJonete93
DBurak191
DFaherie72
DValaa72
DManely191
3 Replies

@Padideh 

Shouldn't Mj.lan and Didan.v have rank 5, and Pandas have rank 4?

If so, in D2:

 

=SUM(IF(($A$2:$A$21=A2)*($C$2:$C$21>=C2),1/COUNTIFS($A$2:$A$21,A2,$C$2:$C$21,$C$2:$C$21)))

 

If you don't have Microsoft 365 or Office 2021, confirm with Ctrl+Shift+Enter.

Then fill down.

best response confirmed by Padideh (New Contributor)
Solution

Yes you are Right.

It works! I Cant thank you enough, I really appreciate your Help

@Hans Vogelaar  

Hi @Padideh 

To help others who search this site please mark @Hans Vogelaar post as solution (link at the bottom of his reply) - Thanks

 

If you run 365, a dynamic array alternative:

=MAP(A2:A21, C2:C21,
    LAMBDA(Grp,Scr,
        SUM(--(UNIQUE(FILTER(C2:C21, A2:A21=Grp)) >= Scr))
    )
)