Sum (array) v Sumproduct (2002 sp3)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

(Edited by HansV to make URL clickable – see Help 19)

See,

http://www.decisionmodels.com/optspeedk.htm%5B/url%5D

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote