I need to execute a Sumproduct using an Offset function with an included Row() function, but it results in a #VALUE! error. Hovering over the error flag says "A value used in the formula is of a wrong data type". The formula works until I add the Row() function. I've tried enclosing the Row() function in an N function, and in an INT function to no avail. Is there any way to work around this? A small sample spreadsheet is attached and here is a brief description.
A1:1 B1: 5
A2:2 B2: 10
These formulas work:
A5: =SUMPRODUCT(A1:A2,B1:B2) ==> 25
A6: =SUMPRODUCT(OFFSET(A1,0,0,2,1),B1:B2) ==> 25
A7: =SUMPRODUCT(OFFSET(A1,7-7,0,2,1),B1:B2) ==> 25
This formula causes the #VALUE! error:
A8: =SUMPRODUCT(OFFSET(A1,ROW()-8,0,2,1),B1:B2) ==> #VALUE!
Note that Evaluate Formula for the formulas in A6, A7 and A8 all produce identical results until the very last step, when A6 and A7 produce the correct answer and A8 generates the error.
Bookmarks