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

[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

 

[T. Valko]

It depends.

At this link:

http://msdn2.microsoft.com/en-us/library/aa730921.aspx

there are some VBA routines that will let you time the calculations. You'll
find the code about 1/4 of the way down.

Biff

 

[JE McGimpsey]

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),D11000)

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)*D11000

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.

 

[Guest]

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