editnamedefinename it something like myrngin the refers to box
=offset($a$1,0,0,counta($A:$A),6)
look at the help index for INDEX to see how to adjust your offset formula.
=sumproduct((myrng="a" etc


--
Don Guillett
SalesAid Software

"sb1920alk" wrote in message
...
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,