Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 (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. #4
    Star Lounger
    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

  5. #5
    3 Star Lounger
    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

  6. #6
    Star Lounger
    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

Posting Permissions

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