The following formula is returning #REF, which seems to be due to the combination of the SUMPRODUCT and OFFSET formulas. Is there any reason why 2 OFFSETs can't return 2 ranges that SUMPRODUCT can evaluate?
=SUMPRODUCT((OFFSET('Source'!$E$37,0,YEAR(F$7)-YEAR($E$7),COUNTA('Source'!C38:C41),0)),(OFFSET('Source'!$M$37,0,YEAR(F$7)-YEAR($E$7),COUNTA('Source'!C38:C41),0)))
The individual components all return values as follows:
Source E37 is a valid reference
Year(...) is actually zero in this column - so no offset of columns or rows from E37 in this case.
COUNTA (..) returns 3 - i.e. 3 rows high
Anyone have any idea on this one?
Thks
=SUMPRODUCT((OFFSET('Source'!$E$37,0,YEAR(F$7)-YEAR($E$7),COUNTA('Source'!C38:C41),0)),(OFFSET('Source'!$M$37,0,YEAR(F$7)-YEAR($E$7),COUNTA('Source'!C38:C41),0)))
The individual components all return values as follows:
Source E37 is a valid reference
Year(...) is actually zero in this column - so no offset of columns or rows from E37 in this case.
COUNTA (..) returns 3 - i.e. 3 rows high
Anyone have any idea on this one?
Thks