Sumproduct() usage

[Jim May]

Now that I understand the Subject formula I use it extensively and in
particular
projects Bringing back multi-criteria data between sheets. The issue I seem
to
be having is that after I create 3 or 4 sheets where there are maybe 2,000
or 4,000
rows using the Sumproduct() many "things" begin to happen. Recalc time
being the first, resorting to Manual Re-calc mode; But now I seem to be
having problems doing the normal File Save function. Has anyone experienced
this situation?

Are there no-no's involved in what I've done? More important is there a
remedy,
apart from a total abandonment of the functions usage?

Thanks in Advance,

 

[Dave Peterson]

The bigger the range and the more formulas you have, the slower recalc will be.

Lots of times, a bunch of =sumproduct()'s could be replaced with a single
pivottable.

And look at:
tools|Options|Calculation tab
(xl2003 menu system)

If you're using manual calculation, you may want to turn off the: "Recalculate
before save" option.

 

[Jim Thomlinson]

Sounds like you have more dependancies than XL can effectively handle. Check
out this link...

http://www.decisionmodels.com/calcsecretsf.htm

There is a bunch more useful stuff on this site. I highly recommend a quick
read of it...