Need Help understanding Formula.

Hi BlackAtlas and welcome to the forum,

The formula counts the number of cells that are alphabetically greater than what is in H2.

No, because the formula you've written won't output the range you've given.

H1 > H1:H10

This checks whether the first term (H1) is greater than whatever is in H1:H10 and returns TRUE or FALSE (by default this will only check the first term in the range though)

(TRUE)+0

adding zero to a boolean forces it to be assessed as a number, 1 for TRUE and 0 for FALSE

SUMPRODUCT will add up the assessments for every value in the range, eg, it will check whether H1 is greater than H1 and add one if it is / zero if it isn't, then it will check if H1 is greater than H2, than H1>H3, etc.

The +1 on the end just increases the value by one.

So what this formula should be outputting (verified by my testing) is 1 for H1>H1:H10 in cell I1, then 2 for H2>H1:H10 in cell I2, etc. counting up 1,2,3,...,9,10 in cells I1:I10

So I don't know how you're getting the offset range starting at 5,6,7 that you're listing as output; the formula you gave shouldn't be producing that.

Let me try again...

the H1>$H$1:$H$10) returns a bunch of True/False terms to if H? > all the stuff in $H$1:$H$10.

The +0 turns the True to a 1 and the False to a zero.

Then you add all these True (1) and False (0) together and add a 1 to the whole total so you never get a zero for an answer.

The "Add Zero" trick turns True and False to numbers so you can add them together.

I hope that makes more sense.