1 Plus Sumproduct Find 1st Instance.

[billandrew]

The below formula part of another thread find 1st Instance. Can someone explain 1 Plus.

D2 =1+SUMPRODUCT(($A$2:$A$10=$A2)*($B$2:$B$10<$B2))

 

[StephenCrump]

Effectively, the formula is saying: for all results in A2:B10 where I have a match in Column A with my (i.e. row 2) Column A value, what is the rank (in ascending order) of my column B value?

If the Sumproduct returns 3, this means that there are 3 Column A matches, with values in column B lower than my value, therefore my rank is 1+3 = 4.

If you could be sure there weren't any duplicate values in column B, you could use:
=SUMPRODUCT(($A$2:$A$10=$A2)*($B$2:$B$10<=$B2))
or
=COUNTIFS($A$2:$A$10,$A2,$B$2:$B$10,"<="&$B2)

 

[billandrew]

This is great.

Thank You very much.