Using a NAME as a reference in another range

[Guest]

* The value in cell A1 varies depending on the number of rows of data brought
down in a query
* I have used the 'NAME' function to name cell A1 '_DataRows'

I now want to use '_DataRows' to identify the end of a range in a SUMPRODUCT
formula. (i.e. a formula something like this...)

=SUMPRODUCT
(--($D$1:$D$"_DataRows"=XYZ),--($D$1:$D"_DataRows"<>""),--($E$1:$E"_DataRows"))


How do I do this (see examples below).

Many Thanks,

Craig

-------------------------------------------

Example 1:
---------------
If cell A1 = 25
Formula in cell B2: =SUMPRODUCT
(--($D$1:$D$25=XYZ),--($D$1:$D$25<>""),--($E$1:$E$25))


Example 2:

 

[Dave Peterson]

Change each portion to something like:
=sumproduct(--(INDIRECT("$D$1:$D$" & _datarows)=XYZ), ....


If you query would return 400 lines at most, why not just use $d$1$1000?

(choose a number that's big enough to never be exceeded--but don't use the whole
column)