STOP a formulae at the FIRST BLANK cell?

[tomwatts]

Hi guys,

I have a spreadsheet function which I want to expand down to the bottom of my spreadsheet BUT I can't because it only works up until the formulae comes accross a blank cell..

My question is this:

Is there a way of keeping the formulae as it is, but when it finds a BLANK cell, to stop at the last cell which contained data???

Below is my formulae:

=SUMPRODUCT((E2:E22<B1001)*(E2:E22>=B1000)*(D2:D22))

Which I want to be:

=SUMPRODUCT((E2:9999<B1001)*(E2:E9999>=B1000)*(D2:D9999))

But because there is blank cells between 23:9999 it comes up with #VALUE!

Thanks,