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

[RockandGrohl]

Hi all,

	A	C	D	E	R	S	T
1	Paper	Rank	Div Rank 1	Div Rank 2	Score	Diversity Score 1	Diversity Score 2
2	Andover	1	#REF !	#REF !	80	80	#REF !
3	Andover	2	#REF !	#REF !	64	54	#REF !
4	Andover	3	#REF !	#REF !	62	62	#REF !

<tbody>
</tbody>

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.