Results 1 to 6 of 6

20070419, 18:05 #1
 Join Date
 Jan 2001
 Location
 Michigan, USA
 Posts
 408
 Thanks
 0
 Thanked 0 Times in 0 Posts
Arrays vs. If Statement (Excel 2002)
A coworker has a large spreadsheet (15,339 KB) that takes a long time to recalculate. She is using the following array on all of the sheets:
{=SUM(IF(($D$9:$D$39=$D44)*($E$9:$E$39=$E44)*($F$9 :$F$39=$F44),P$9:P$39))}. I have never used an array and was trying to see if I could convert it to an IF Statement to see if it would speed up the recalculation but I am getting lost in the IF/ANDs. Could someone 1.) help me convert this to an IF statement and 2.) explain if/why an array would be better?
This is what I was trying with the IF statement  =IF(AND(SUM(D939=D44),SUM(E9:E39=D44),SUM(F9:F39=F44)),SUM(P9:P3 9),"") but all I get is #VALUE.
TIA<font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

20070419, 18:27 #2
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: Arrays vs. If Statement (Excel 2002)
I think it is a question of Parentheses: =IF(AND((SUM(D939)=D44),(SUM(E9:E39)=D44)),IF(SUM(F9:F39)=F44,SUM (P9:P39),""))

20070419, 18:30 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Arrays vs. If Statement (Excel 2002)
Such a formula is used to sum values conditionally. For a single condition, one can use the SUMIF function, but for multiple conditions, the most frequently used methods are
1) Array formulas such as the one you mention, using =SUM(IF(...
and
2) Standard (nonarray) formulas using the SUMPRODUCT function. Your example would become
=SUMPRODUCT(($D$9:$D$39=$D44)*($E$9:$E$39=$E44)*($ F$9:$F$39=$F44)*P$9:P$39)
You cannot convert the formula to something like =IF(AND(..., it simply won't return the result you want.
The idea behind both the array and the nonarray formula is that TRUE = 1 and FALSE = 0 in Excel. The expression $D$9:$D$39=$D44 results in an array of 31 TRUE/FALSE values, indicating whether each cell in D939 is equal to D44. This is equivalent to an array of 31 ones and zeros. The arrays for the various conditions are multiplied, this is done element by element. Since 0 times anything is 0, the only way to get a 1 in the product array is if every contributing value is 1, i.e. if all conditions are TRUE. Only these are included in the sum.

20070420, 03:10 #4
 Join Date
 May 2002
 Location
 Brisbane, Queensland, Australia
 Posts
 87
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Arrays vs. If Statement (Excel 2002)
Hans,
Thanks for the great explanation on SUMPRODUCT function. I have a question.
How does the last element work as it has no condition? i.e. "P$9:P$39" element.
Thanks
Matthew

20070420, 03:26 #5
 Join Date
 Jan 2001
 Location
 Ankeny, Iowa, USA
 Posts
 298
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Arrays vs. If Statement (Excel 2002)
Matthew,
P9:P39 contains the numbers that would be summed if all the other conditions are true...effectively 1 x 1 x 1 times whatever number is in the relevant row in P.
Brett

20070420, 03:36 #6
 Join Date
 May 2002
 Location
 Brisbane, Queensland, Australia
 Posts
 87
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Arrays vs. If Statement (Excel 2002)
Thanks.
I've only used SUMPRODUCT as a multiple condition COUNT function.....hadn't really thought about the ability to SUM. The function name should have been a dead give away. <img src=/S/doh.gif border=0 alt=doh width=15 height=15>
Regards,
Matthew