Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct against worksheet vs named range- any speed difference?
I have a variety of sumproducts that are pulling from a large (30K row)
range from a second sheet. I'm wondering whether it would make any difference if I made the range a named range instead; the key benefit I see is that I could dynamically change the size of the range (Bullen's autoexpanding named range) so that I'm not using my formulas against a larger (conservatively estimated) range. If I do that, will pulling this data into a sumproduct as a named range vs a direct sheet reference make any speed difference? Thanks, Keith |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct against worksheet vs named range- any speed difference?
I doubt it would make much of a difference, the problem is the
(A2:A30000=x)AND(B2:B30000=y) being part of the sumproduct or any array formula You would probably be better off using 30k AND formulas then use a simple COUNTIF to total them =AND(A2=x,large_B2=y) (assume you use these help formulas in D2:D30000) copy down 30000 rows then use =COUNTIF(D2:D30000,TRUE) -- Regards, Peo Sjoblom "Keith R" wrote in message ... I have a variety of sumproducts that are pulling from a large (30K row) range from a second sheet. I'm wondering whether it would make any difference if I made the range a named range instead; the key benefit I see is that I could dynamically change the size of the range (Bullen's autoexpanding named range) so that I'm not using my formulas against a larger (conservatively estimated) range. If I do that, will pulling this data into a sumproduct as a named range vs a direct sheet reference make any speed difference? Thanks, Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting a named range to appear in another worksheet | Excel Discussion (Misc queries) | |||
Sumproduct Indirect Named Dynamic Range using Offset | Excel Worksheet Functions | |||
Removing `named range´ from worksheet | Excel Discussion (Misc queries) | |||
Using Named Range with protection on the worksheet | Excel Discussion (Misc queries) | |||
toggling which worksheet a named range refers to | Excel Discussion (Misc queries) |