Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Toowoomba, Queensland, Australia
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sum (array) v Sumproduct (2002 sp3)

    I often need to get conditional sums from tables of data. As far as I can tell, using SUMPRODUCT with a series or ranges and criteria is equivalent to to using SUM as an array formula with the same settings.

    e.g.,To total all shipments from a particular Distribution centre I can use =SUMPRODUCT(Qty*(Ship_From=A7)) or {=SUM((Qty*(Ship_From=A7)))} Where Qty and Ship_From are named ranges in my table.

    Is there any advantage in using one version over another especially with regard to recalculation times?

    Thanks

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

    Re: Sum (array) v Sumproduct (2002 sp3)

    I did a test with two arrays of 65535 elements each, with various conditions. SUMPRODUCT recalculated consistently faster, but the difference was not dramatic, varying from 5% to 15%.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    Toowoomba, Queensland, Australia
    Posts
    112
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum (array) v Sumproduct (2002 sp3)

    Thanks for the response. I think I'll stick with SUMPRODUCT until I can get my head around the approach given on the decisionmodels site.

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

    Re: Sum (array) v Sumproduct (2002 sp3)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    See,

    http://www.decisionmodels.com/optspeedk.htm

  5. #5
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sum (array) v Sumproduct (2002 sp3)

    Just in case: Your example

    =SUMPRODUCT(Qty*(Ship_From=A7)) or {=SUM((Qty*(Ship_From=A7)))}

    is not-multi-conditional. A SumIf formula is then more appropriate for it is faster:

    =SUMIF(Ship_From,A7,Qty)
    Microsoft MVP - Excel

Posting Permissions

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