If you are going against thousands of rows there is probably not much you can
do... You can use dynamic named ranges but since they rely on the offset
function this will make all of your sumproduct formulas volatile (recalculate
every time there is a calculation whether the dependant range has been
dirtied or not). That being said you may already have more than 65,536
dependancies at which point Excel stops doing it's smart calc and
reclaculates everything anyways. The long and the short of it is that this is
probably going to be slow. You can either turn calculations off and recalc as
necessary or you could just live with it... (you could use code to make the
ranges dynamic without the offset function, but once again you may already be
over the 65,536 dependancies limit and Excel will have abandoned doing smart
calcs so this route may have little or no effect).
--
HTH...

Jim Thomlinson


"sb1920alk" wrote:

I have about 10 columns whose calculations use the sumproduct formula based
on the entries in most of the previous columns. So the 10th column has about
6 critieria in the sumproduct formula. In an ideal world, each of the
critieria would refer to thousands of rows, but this makes the calculations
very slow. Even limiting it to 500 rows, there is still a ~1 second delay.

I would like the file to be able to hold thousands of entries, but not waste
time calculating empty rows.

Do you think it would help the calculation speed if I set up dynamic named
ranges to use as references in my sumproduct calculations? Or do you think it
would take even longer because the Excel would need to repeatedly calculate
the size of the dynamic range?

I appreciate any input on this.

Thanks,