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
|