Ranking Score Based On Sequence Date

hi all..

how to make rank formula based on score and earliest date sequence
the score can given rank is always 100 except 100 not given rank
date format in dd/mm/yyyy
here this my attachment file
any help, greatly appreciated..

note:
i need formula not use tricks, vba, pt
similar problem https://www.mrexcel.com/board/thread...e-100.1155222/

john m

Try this in H4:

=IF(F5<>100,"",COUNTIFS($F$4:$F$8,100,$G$4:$G$8,"<"&G5)+1)

hi..still not work

Try again:

=IF(F4<>100,"",COUNTIFS($F$4:$F$8,100,$G$4:$G$8,"<"&G4)+COUNTIFS($F$4:F4,100,$G$4:G4,G4))

For 365 version, try this at H4:

="Rank-"&MATCH(($F$4:$F$8)+(DATEDIF($G$4:$G$8,"1/1/2022","d")/1000),AGGREGATE(14,4,($F$4:$F$8)+(DATEDIF($G$4:$G$8,"1/1/2022","d")/1000),ROW($A$1:$A$5)),0)

Not sure if it works for other versions. Please check it out.