Sumproduct Dynamic Range - XL2003



I have a sumproduct formula(s) that is calculating very slowly in my

=SUMPRODUCT(((Data!$D$2:$D$10000=$E22)*(Data!$A$2:$A$10000='H-Code SS

I'd like to insert a dynamic range to improve performance, but haven't had
any luck. Any suggestions would be greatly appreciated.

Paul C

Rather then try to dynamically do each range in your SUMPRODUCT formula,
utilize dynamically defined range names.

ie SUMPRODUCT(--(range1=2),range2)

Debra Dalgleish's Contextures site has a good section on establishing
dynamic names

One caution, make sure you tie each dynamic name to the same marker to keep
them identical sizes or the SUMPRODUCT will error.

Jim Thomlinson

That method of creating a dynamic named range uses offset which is volatile.
That means that you are actually adding calculation overhead by doing this.
That will make this even slower... (while that calculation will be faster it
will be calculated much more often)

While I have not tried this it should work. Instead of using offset as your
function try

=(Sheet1!$A$1:INDEX(Sheet1!$A:$A, COUNTA(Sheet1!$A:$A)))

Which uses index which is not volatile. This does create a dynamic named
range. The only question I have is whether named ranges are volatile by
default. perhpas someone else can settle that...

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question