Sumproduct formulas & slow response
 

I have a spreadsheet that is about 8,500 KB that is painfully slow to
calculate. While I can certainly set it manual calculation, my preference is
to leave the calculation set to automatic. In doing a little testing on the
file I discovered that the culpret seems to be multiple SUMPRODUCT formulas.

I am using it in about 30 rows which include monthly amounts for 7 years, so
there are over 2500 sumproduct formulas, each usind 3 different factors in
the formula (e.g.
=SUMPRODUCT(($C$7:$C$4426=2005)*($I$7:$I$4426="30" )*($K$7:$K$4426="54")*(CT$7:CT$4426)).

I've tried using the -- convention in the cells but it doesn't seem to speed
it up.

Another bit of information is that I have a similar, yet 3X larger file,
using complicated SUMIF formulas to do the same type of calculations which
doesn't have this slow calculation issue. I can switch back to the SUMIF
method if I have to but SUMPRODUCT seems much cleaner and makes identifying
formula errors much easier.

Anybody have any suggestions as to how to speed up the calcualtions?

Thanks for any help

Sumproduct formulas & slow response
 

Darby,

Scrap your SUMPRODUCT formulas and use a pivot table. Much faster, much
more stable, much more auditable, etc., etc. AND you never need to worry
about formula errors.

HTH,
Bernie
MS Excel MVP


"Darby" wrote in message
...
I have a spreadsheet that is about 8,500 KB that is painfully slow to
calculate. While I can certainly set it manual calculation, my preference
is
to leave the calculation set to automatic. In doing a little testing on
the
file I discovered that the culpret seems to be multiple SUMPRODUCT
formulas.

I am using it in about 30 rows which include monthly amounts for 7 years,
so
there are over 2500 sumproduct formulas, each usind 3 different factors in
the formula (e.g.
=SUMPRODUCT(($C$7:$C$4426=2005)*($I$7:$I$4426="30" )*($K$7:$K$4426="54")*(CT$7:CT$4426)).

I've tried using the -- convention in the cells but it doesn't seem to
speed
it up.

Another bit of information is that I have a similar, yet 3X larger file,
using complicated SUMIF formulas to do the same type of calculations which
doesn't have this slow calculation issue. I can switch back to the SUMIF
method if I have to but SUMPRODUCT seems much cleaner and makes
identifying
formula errors much easier.

Anybody have any suggestions as to how to speed up the calcualtions?

Thanks for any help