Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Sumproduct with Offsets

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    =SUMPRODUCT((range1,range2))

    ...should be...

    =SUMPRODUCT(range1,range2)


  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    New Member
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  8. #8
    New Member
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •