Dynamic sumproduct range specifier

[UHsoccer]

I use a number of sumproduct formulas that have the start and end cell row hardcoded such as

=(SUMPRODUCT(
--((Closed!$AP$4:$AP$1500)=$B6),
--(MONTH(Closed!$A$4:$A$1500)=D$1),
    Closed!$H$4:$H$1500))/1000

The end cell in this case is 1500

How can I specify to use the end of available data data in column AP, A and H respectively instead of using the 1500? Next month it may need to be 1800, etc

 

[Aladin Akyurek]

One question out of curiosity:

Why does the MATCH statement refrerences MATCH(9.999999E+307

From my engineering background, that is one heck of a big number, would 65500 not suffice?

That you ask about 9.999999E+307 informs me that you opted for an inefficient option...

Here is a link on 9.99999999999999E+307...

http://tinyurl.com/83b2x