Sumproduct Dynamic Range - XL2003

[Nate]

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

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

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

http://www.contextures.com/xlNames01.html#Dynamic

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...

 

[Roger Govier]

Hi Jim

I agree entirely. I always use INDEX to create Dynamic Ranges.
Debra kindly put a tutorial I wrote about this on her site
http://www.contextures.com/xlNames03.html