CODEWITHSUNDEEP
excelrank
Andrew Abbott
Andrew Abbott

Reputation: 437

Rank duplicate text values in Excel by order of occurrence for unique criteria

In Excel I have results data that is ordered in a list similar to below. I am looking for a formula to rank the 'class' column. I have come up with the following which seems to work, but without considering the 'unique' RaceID column.

={COUNTIF(Class,"<"&C2)+SUM(IF(C2=$C$2:C2, 1, 0))-(SUMPRODUCT(--(C2>C$2:C$11))+1)}

Where 'Class' refers to 'C:C'.

Unfortunately this would produce a result of '4' for Class A in cell E7, even though Daniel was the race winner for RaceID 0002.

The fifth column is the desired output.

Col A     Col B          Col C    Col D     Col E
RaceID    Overall Pos    Class    Name      Class rank
0001      1              A        Jack      1
0001      2              A        Matt      2
0001      3              A        Daniel    3
0001      4              B        Gordon    1
0001      5              B        Phillip   2
0002      1              A        Daniel    1
0002      2              A        Matt      2
0002      3              B        Günther   1
0002      4              B        Gordon    2
0002      5              A        Jack      3

What I need is to be able to extend this formula to only rank when the RaceID values match the current row. I'm sure this shouldn't be that difficult, but I'm struggling to complete the formula.

Upvotes: 4

Views: 2467

Answers (1)

Dirk Reichel
Dirk Reichel

Reputation: 7979

If I got you correct, then this non-array formula will do:

=COUNTIFS(A:A,A2,B:B,"<"&B2,C:C,C2)+1

EDIT

If your excel does not support COUNTIFS then also this formula will do:

{=SUM(IF(($A$1:INDEX(A:A,MATCH("zzz",D:D))=A2)*($C$1:INDEX(C:C,MATCH("zzz",D:D))=C2),$B$1:INDEX(B:B,MATCH("zzz",D:D))<B2)*1)+1}

This is an array formula and must be confirmed with ctrl+shift+enter.

Upvotes: 1

Related Questions