Sumproduct formulas & slow response

[Darby]

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

 

[Bernie Deitrick]

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