Results 1 to 12 of 12

20041005, 18:10 #1
 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 twocondition 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.

20041005, 18:15 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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)

20041005, 19:16 #3
 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?

20041005, 19:33 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20041006, 08:03 #5
 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?

20041006, 08:09 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20041006, 08:28 #7
 Join Date
 Feb 2003
 Posts
 89
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Conditional Sum Error 1004 (Excel 2000)
Thankyou Hans, it's late here so I'll read through that link in the morning.

20041006, 13:28 #8
 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

20041006, 13:44 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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)

20041006, 19:07 #10
 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!

20041006, 19:16 #11
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 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.

20041006, 19:22 #12
 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 futureit's great once you get the idea down.