Sumproduct with Offsets

RaoulF

New Member
Joined
Mar 4, 2002
Messages
17
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
You're inappropriately using commas as a union operator...

=SUMPRODUCT((range1,range2))

...should be...

=SUMPRODUCT(range1,range2)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top