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
|