Rank repeated text values ​​in Excel in order of appearance for unique criteria

Question

In Excel, I have results data that are ordered in a list like the one below. I am looking for a formula to rank a class column. I came 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 will lead to a “4” result for class A in cell E7, although Daniel was the winner of the race in RaceID 0002.

The fifth column is the desired result.

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

I need to be able to extend this formula only to ranking when the RaceID values ​​match the current row. I am sure it should not be so difficult, but I am struggling to fulfill the formula.

Answer 1

, :

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

excel COUNTIFS, :

{=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}

ctrl + shift + enter.