SUMPRODUCT reference changes!!! Not what I want...

[Joe M.]

I am using SUMPRODUCT to sum a column on another worksheet. My formula looks
like this:

=SUMPRODUCT((Data!$E$4:$E$10000=$A$1)*(Data!$H$4:$H$10000=$A4)*(Data!$D$4:$D$10000="Y")*(Data!$J$4:$J$10000="Y"))

The # of rows on worksheet DATA changes perodically. When I delete the rows
in DATA and paste in new rows the "to" reference changes to the same as the
"from" reference:

=SUMPRODUCT((Data!$E$4:$E$4=$A$1)*(Data!$H$4:$H$4=$A4)*(Data!$D$4:$D$4="Y")*(Data!$J$4:$J$4="Y"))

What can I do to keep my "to" reference at 10000?

Thanks,
Joe M.

 

[Don Guillett]

I would make defined name ranges
colE
=offset($e$3,1,0,counta($e:$e),1)
etc then just juse colE=

 

[Joe M.]

I have created name ranges for col E, H & J.

=SUMPRODUCT((ColE=$A$1)*(ColH=$A4)*(ColJ="Y"))

But the result of the formula is now #N/A

What did I do wrong?

Thanks,
Joe M.

 

[Don Guillett]

Dunno without seeing what you did or

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.