Sumproduct with Offsets

[RaoulF]

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

 

[RaoulF]

Aladin

You were right, it turns out that OFFSET prefers a blank as the width argument to a zero. This gave me the two columnar ranges that I was looking for.

The closest that 'Help' gets to being helpful here is that : "Width must be a positive number." I'm not sure if a purist would say that that technically includes or excludes zeros but now we know.

Thanks for your help, and Mark I appreciate your efforts too - I'm keen to follow up that union reference operator now - in conjuction with using labels in formulas I'm sure this could replace lookups with index/ match in some circumstances?
This message was edited by RaoulF on 2002-03-21 04:14