Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Dynamic Range - XL2003
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Dynamic Range - XL2003
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. -- If this helps, please remember to click yes. "Nate" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Dynamic Range - XL2003
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... -- HTH... Jim Thomlinson "Paul C" wrote: 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. -- If this helps, please remember to click yes. "Nate" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumproduct Dynamic Range - XL2003
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 -- Regards Roger Govier Jim Thomlinson wrote: 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using sumproduct in a dynamic range? | Excel Discussion (Misc queries) | |||
Need help please-SUMPRODUCT and Dynamic Range | Excel Worksheet Functions | |||
Sumproduct Indirect Named Dynamic Range using Offset | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Dynamic range names, multiple criteria, sumproduct | Excel Discussion (Misc queries) |