# Thread: Sum (array) v Sumproduct (2002 sp3)

1. ## 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. ## 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. ## 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. ## 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. ## Re: Sum (array) v Sumproduct (2002 sp3)

=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)

#### Posting Permissions

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