Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: #Value

  1. #1
    New Member
    Join Date
    Sep 2013
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default #Value

    Hey folks,

    =SUMIFS('June & Before:December'!F:F,'June & Before:December'!A:A,"1342-79002570",'June & Before:December'!C:C,"Payment",'June & Before:December'!I:I,"0-60 days")

    whe the abovementioned formula is displaying value error.

  2. #2
    New Member
    Join Date
    Sep 2013
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #Value

    P.S: 'June & Before' and 'December' are sheet names and there are several sheets in between them.

  3. #3
    Board Regular
    Join Date
    Nov 2013
    Posts
    524
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #Value

    click on the cell with the formula

    click the fx button at left end of formula bar

    the popup dialog may point you to the source of the error


    maybe the sum range is meant to be only one cell, not the whole column
    Last edited by jsotola; Dec 20th, 2013 at 03:00 AM.

  4. #4
    New Member
    Join Date
    Sep 2013
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #Value

    it doesnt point to any error..

  5. #5
    Board Regular
    Join Date
    Nov 2013
    Posts
    524
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #Value

    so the "function arguments" dialog box is showing expected values on right side?

    nothing in red?

  6. #6
    New Member
    Join Date
    Sep 2013
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #Value

    not for "sum_range" it shows blank

  7. #7
    Board Regular
    Join Date
    Nov 2013
    Posts
    524
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #Value

    that is the destination for the result

    maybe your data is not numeric

  8. #8
    New Member
    Join Date
    Sep 2013
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #Value

    nop its numeric.

  9. #9
    Board Regular XOR LX's Avatar
    Join Date
    Jul 2012
    Location
    Turin, Italy
    Posts
    4,474
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #Value

    Hi,

    You cannot perform the 3D sum as you currently have it laid out. You would require (I am guessing at your other intermediary sheet names here):

    =SUMPRODUCT(SUMIFS(INDIRECT("'"&{"June & Before","July","August","September","October","November","December"}&"'!F:F"),INDIRECT("'"&{"June & Before","July","August","September","October","November","December"}&"'!A:A"),"1342-79002570",INDIRECT("'"&{"June & Before","July","August","September","October","November","December"}&"'!C:C"),"Payment",INDIRECT("'"&{"June & Before","July","August","September","October","November","December"}&"'!I:I"),"0-60 days"))

    Even better, since this is quite ungainly and inflexible, would be to go to Name Manager and create a new name, Sheet_Names say, with this in the Refers to: box:

    ={"June & Before","July","August","September","October","November","December"}

    or whatever the actual sheets are in queston.

    The formula then becomes:

    =SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheet_Names&"'!F:F"),INDIRECT("'"&Sheet_Names&"'!A:A"),"1342-79002570",INDIRECT("'"&Sheet_Names&"'!C:C"),"Payment",INDIRECT("'"&Sheet_Names&"'!I:I"),"0-60 days"))

    That way, if ever you need to add/change/delete a sheet from the summation, you can simply make the necessary amendments in the defined name, and the formula will update accordingly.

    Regards
    Last edited by XOR LX; Dec 20th, 2013 at 05:41 AM.

  10. #10
    New Member
    Join Date
    Sep 2013
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #Value

    first off thanks alot as it worked for me,but if you could tell how "names manager" works.
    much obliged,
    thanks in anticipation,

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
  •