Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Sum Error 1004 (Excel 2000)

    I have a large table A>AH, 7500 lines that I am attempting to use the Conditonal SUm Wizard with. I get all the way through the two-condition setup. There is an answer visible in the dialog window. I indicate the two locations for the conditonal parameters. They appear. Then, when I indicate the location for the "answer" I get:
    Run Time Error 1004: Unable to set the FormualArray property of the Range Class.
    The Help is not useful in finding out why I can see an answer and yet it cannot place the formula so I can study and amend it.
    I attempted this formula =SUMPRODUCT((Data!H:H,"<=183003")*(Data!C:C,"<1700 00")*Data!J1:J10000) to sum asset numbers less than or equal to 183003 in Col H and Account numbers under 170000 in column C, for Costs in Column J. It did not work, so any comments correcting it would be appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Sum Error 1004 (Excel 2000)

    You're confusing the syntax for SUMIF with that for SUMPRODUCT. Try this:

    =SUMPRODUCT((Data!H1:H10000<=183003)*(Data!C1:C100 00<170000)*Data!J1:J10000)

    The conditions are used directly instead of as a string, and all three parts have the same size here (rows 1:10000)

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Sum Error 1004 (Excel 2000)

    That solved the problem! Thanks. Saves alot of sorting,etc. What is the difference between SUMPRODUCT and using an Array formula? Where is one preferable to another? Can an array formula return the same result as this one?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Sum Error 1004 (Excel 2000)

    SUMPRODUCT is a worksheet function that has array functionality built in, so in fact, you are using an array formula without having to use Ctrl+Shift+Enter. You could also use an array formula in this situation:

    =SUM((Data!H1:H10000<=183003)*(Data!C1:C10000<1700 00)*Data!J1:J10000)
    or
    =SUM(IF((Data!H1:H10000<=183003)*(Data!C1:C10000<1 70000);Data!J1:J10000))

    confirmed with Ctrl+Shift+Enter. I prefer the SUMPRODUCT here because I don't have to use CTRL+SHIFT+ENTER, but I don't think one is "better" than the other. Perhaps other Loungers will disagree.

    Array formulas can be used in many more situations than SUMPRODUCT.

  5. #5
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Sum Error 1004 (Excel 2000)

    Hans,

    I'm curious as to your last statement,

    "Array formulas can be used in many more situations than SUMPRODUCT."

    Can you elaborate further, if you have the time?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Sum Error 1004 (Excel 2000)

    See for example Using Array Formulas in Excel by Bob Umlas for some of the nifty things you can do with array formulas.

  7. #7
    Star Lounger
    Join Date
    Feb 2003
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Sum Error 1004 (Excel 2000)

    Thank-you Hans, it's late here so I'll read through that link in the morning.

  8. #8
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Sum Error 1004 (Excel 2000)

    Can I amend the formula to reflect a range? Instead of saying "less than 183003" can I also say "between 150000 and 170000" within the formula? I am going to experiment, yet I thought it would be a good general question to ask. TYIAMH

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Sum Error 1004 (Excel 2000)

    Replace

    (Data!H1:H10000<=183003)

    in the formulas by

    (Data!H1:H10000>=150000)*(Data!H1:H10000<=170000)

  10. #10
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Sum Error 1004 (Excel 2000)

    OK. Thank you! How would one then say the opposite: instead of between 150000 and 170000, saying lower than 150000 and higher than 170000. I've tried switching the >=s around but am getting funny numbers. I was reading somewhere about + used for OR, but that's one of the setups that is not getting me the correct result. Thanks again!

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Sum Error 1004 (Excel 2000)

    If the conditions are mutually exclusive, you CAN use + for OR, but you have to take care to place the brackets correctly:

    =SUMPRODUCT(((Data!H1:H10000<150000)+(Data!H1:H100 00>170000))*(Data!C1:C10000<170000)*Data!J1:J10000 )

    Note that there is an extra pair of brackets around the sum

    ((Data!H1:H10000<150000)+(Data!H1:H10000>170000))

    Data!H1:H10000<150000 can be 0 (false) or 1 (true), and Data!H1:H10000>170000 can also be 0 or 1. Theoretically, the sum could be 0, 1 or 2, but since it is impossible that a number is less than 150000 AND greater than 170000, the sum is never equal to 2. So the sum is 0 if both conditions are false, and 1 if one of them is true.

  12. #12
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Sum Error 1004 (Excel 2000)

    Thanks a million. That worked. The explanation shows why those extra parens are needed, and it makes sense. Hope to use SUMPRODUCT more in the future-it's great once you get the idea down.

Posting Permissions

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