Unique Rank value for a subgroup within a group

The good news is that you can throw away the SUMPRODUCT function and replace it with a pair of COUNTIFS functions. The COUNTIFS can use full column references without detriment and is vastly more efficient than the SUMPRODUCT even with the SUMPRODUCT cell ranges limited to the extents of the data.

In N2 as a standard function,

=COUNTIFS(B:B, B2,M:M, "<"&M2)+COUNTIFS(B$2:B2, B2, M$2:M2, M2)

Fill down as necessary.

  Filtered Results

Solution basing on OP

Studying your post demanding to post any alternatives, I got interested in a solution based on your original approach via the SUMPRODUCT function. IMO this could show the right way for the sake of the art:

Applied method

Get

• a) all current ids with a group value lower or equal to the current value

  MINUS

• b) the number of current ids with the identical group value starting count from the current row

  PLUS

• c) the increment of 1

Formula example, e.g. in cell N5:

=SUMPRODUCT(($B$2:$B$38705=$B5)*($M$2:$M$38705<=$M5))-COUNTIFS($B5:$B$38705,$B5,$M5:$M$38705,$M5)+1

P.S.

Of course, I agree with you preferring the above posted solution, too :+)