Sumproduct against worksheet vs named range- any speed difference?

[Keith R]

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

 

[Peo Sjoblom]

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 D230000)

copy down 30000 rows then use


=COUNTIF(D230000,TRUE)