Thread: Arrays vs. If Statement (Excel 2002)

1. Arrays vs. If Statement (Excel 2002)

A co-worker 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

2. 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),""))

3. 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 (non-array) 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 non-array 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.

4. 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

5. 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

6. 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

Posting Permissions

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