Sumproduct formula for Ranking is returning #REF! Next to a SumProduct that is working perfectly.

[RockandGrohl]

Hi all,

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Paper[/TD]
[TD]Rank[/TD]
[TD]Div Rank 1[/TD]
[TD]Div Rank 2[/TD]
[TD]Score[/TD]
[TD]Diversity Score 1[/TD]
[TD]Diversity Score 2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Andover[/TD]
[TD]1[/TD]
[TD]#REF ![/TD]
[TD]#REF ![/TD]
[TD]80[/TD]
[TD]80[/TD]
[TD]#REF ![/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Andover[/TD]
[TD]2[/TD]
[TD]#REF ![/TD]
[TD]#REF ![/TD]
[TD]64[/TD]
[TD]54[/TD]
[TD]#REF ![/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Andover[/TD]
[TD]3[/TD]
[TD]#REF ![/TD]
[TD]#REF ![/TD]
[TD]62[/TD]
[TD]62[/TD]
[TD]#REF ![/TD]
[/TR]
</tbody>[/TABLE]


In Column C, the formula is:

=SUMPRODUCT((A4=$A:$A)*(R4<$R:$R))+1

(Previously I had this as $A$4:$A$5128 which also worked)

So this formula goes all the way down and correctly ranks column R which is the score (gotten from a Sum) Column R contains a live formula and this ranks correctly


In column D, the formula is:

=SUMPRODUCT((A4=$A:$A)*(S4<$S:$S))+1

Even if I give the cells an absolute range to look at, I keep getting a #REF ! result.



Column S has a small formula that looks at the rank and determines something, don't worry about that, however, it seems the SUMPRODUCT formula in Column D is having a hard time displaying a ranking result from the scores in Column S.


The weird part is if I copy column S's score into a whole new sheet and put the formula in for another column, it calculates perfectly and gives a correct rank.

Any ideas why I'm getting the #REF ! error? Thank you.

 

[steve the fish]

Id imagine theres a reference error cell in either column A, column S or both.

 

[RoryA]

I suspect that somewhere in column S you have a #REF ! error.

 

[RockandGrohl]

Id imagine theres a reference error cell in either column A, column S or both.

Yeah, you're right! - can't believe that slipped through the net!

Thank you.