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

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

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

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

"Neophyte" wrote in message
...
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

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

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?

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

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"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?
Thanks,
Lee