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 |
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 |
All times are GMT +1. The time now is 07:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com