RockandGrohl
Well-known Member
- Joined
- Aug 1, 2018
- Messages
- 790
- Office Version
- 365
- Platform
- Windows
Hi all,
<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.
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.