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/micro...heet.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.


In article ,
"Neophyte" wrote:

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?