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

[Mark W.]

You're inappropriately using commas as a union operator...

=SUMPRODUCT((range1,range2))

...should be...

=SUMPRODUCT(range1,range2)

[RaoulF]

Thanks for the prompt response as ever Mark, but either the message board has converted some other character into a comma or I haven't noticed the subtle difference between those 2 versions.

Thanks in advance

[Mark W.]

On 2002-03-20 10:28, RaoulF wrote:
Thanks for the prompt response as ever Mark, but either the message board has converted some other character into a comma or I haven't noticed the subtle difference between those 2 versions.

Thanks in advance

When "range1,range2" is enclosed by parentheses...

(range1,range2)

...it is treated as a single reference created by the union of range1 and range2. See the Excel Help topic for "Calculation operators in formulas" and look specifically at the "Reference operator" section. Notice a comma is the union reference operator. This should not be confused with the use of comma as a separator of arguments in a function's argument (parameter) list.

=SUMPRODUCT(range1,range2) has 2 arguments.
=SUMPRODUCT((range1,range2)) has only 1.

I've never sucessfully used the union operator directly in a worksheet formula, but they are quite handy in defining a range name of discontiguous data (e.g., myCells may refer to =A1,C4,D19).

Simplify your SUMPRODUCT formula by removing all unnecessary parentheses and see if this solves your problem.

[ This Message was edited by: Mark W. on 2002-03-20 10:46 ]

[Mark W.]

Wait a minute! #REF! can also be caused by an OFFSET function that produces a reference that's "over the edge of the worksheet". By chance is YEAR(F$7)-YEAR($E$7) producing a value well in excess of 255.

[RaoulF]

I have not yet checked the help (it's getting late here), but I have simplified the formula as follows:

=SUMPRODUCT(OFFSET('Source'!$E$37,0,0,3,0),OFFSET('Source'!$M$37,0,0,3,0))

I think that's eliminated all excess brackets and also got rid of the calculations re: the rows, columns and height. Unfortunately it's still showing #Ref??

(In answer to your additional post, the year formula was returning zero here.)



[ This Message was edited by: RaoulF on 2002-03-20 10:50 ]

[Mark W.]

Did you see my last reply? See if that's the problem.

[RaoulF]

I have eliminated the Year(..) formula - it was a zero in this case anyway??

[Mark W.]

What's in Source!C38:C41? If COUNTA(Source!C38:C41) returns 0 you're requesting a reference of 0 height which is would be a problem too.

[ This Message was edited by: Mark W. on 2002-03-20 11:08 ]

[Aladin Akyurek]

On 2002-03-20 10:10, RaoulF wrote:
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

Raoul,

What do you expect OFFSETs to return: a columnar range in E and another in M?

If so, have a close look at the args of OFFSET:

=OFFSET(Source!$E$37,0,n1,n2,0)

where the last zero is troublesome (will lead to a #REF! error.

Aladin

[ This Message was edited by: Aladin Akyurek on 2002-03-20 12:40 ]