Conditional RANK using dynamic array function

Hi,

I have a table with three columns and need to create a conditional rank, like this:

=SUMPRODUCT(($A$2:$A$67=$A2)*($C$2:$C$67=$C2)*($B$2:$B$67<$B2)/COUNTIFS($A$2:$A$67,$A$2:$A$67,$C$2:$C$67,$C$2:$C$67,$B$2:$B$67,$B$2:$B$67))+1

This formula needs to be copied down and adjusted to cover the whole dataset and it will quickly become heavy to calculate.

Does anybody have an alternative formula using dynamic array functions that produce a spill range to cover the dataset?

Best regards,
Marbleking

Hi Bo_Ry,

Thanks for this. Your formula gives different results than the one I had before when there are duplicate rows. Please see attached workbook with slightly different raw data. The first one I sent contained only unique rows, which isn't always the case.

Best regards,
Marbleking

Thanks, Bo_Ry! Nice!