Which is faster sum(if) as an array or sumproduct?

  • Thread starter Thread starter Neophyte
  • Start date Start date
N

Neophyte

I finally got my sum(if) based on more than one condition to work but it
takes my 1.8 Ghz chip 7 or 10 minutes to calculate the workbook with the
array formulas in 2003 Excel. Is Sumproduct any faster or do I just need to
use the free time to do something else?
Thanks,
Lee
 
In general, SUMPRODUCT is faster than SUM(IF()). How much faster depends
on the exact implementation, but anecdotally I've seen from less than 1%
to as much as 50%. SUM(IF( bloats your file more, IIRC. There's some XL
version dependence as well.

See this old thread for some discussion:

http://groups.google.com/group/microsoft.public.excel.worksheet.functions
/browse_thread/thread/1cb52ef08e1ade2d/ea1bc70584c4e587

It's hard to say without knowing more about your layout, but you may see
dramatic gains if you can use helper columns/rows to do partial array
calculations so that the array formulae don't recalculate as much or as
often. For instance, with

=SUMPRODUCT(--(A1:A1000=1),--(B1:B1000=2),--(C1:C1000=3),D1:D1000)

If columns A and B don't change very often, but column C does, using an
extra column with (array-entered):

E1:E1000: =(A1:A1000=1)*(B1:B1000=2)*D1:D1000

Then a final column:

F1:F1000 =SUMPRODUCT(--(C1:C1000=3),E1:E1000)

Will require only two arrays be evaluated instead of four when a value
in column C is changed.
 
For what it's worth I just built a spreadsheet with 70,000 dependencies, all
of which are SUMPRODUCT functions summarizing a large table of data in
various ways. It recalculates instantly, and this is one a machine running
XL 2000 with 256mb of RAM.

So it seems a safe bet that with your configuration SUMPRODUCT would also be
quick.

Dave
 
Back
Top