I have used sumproduct in my worksheet. It looks like following:
=SUMPRODUCT(($P$3:$P$7734="P1 Howard") * ($H$3:$H$7734=DATEVALUE("5/1/2006")))

It works fine. I have to use this formula for various dates and teams. The
report using this formula is generated everyday. I have column "A" showing
Loan numbers which is the only column that can not be null. Is there any
formula I can put to replace the range. The range is not known and changes
everyday. I want it to go to the end of file (based on column A). That way I
don't have to change the range manually and report can be updated dynamically
everyday. Thanks for your help.

Purnima Sharma

You can use Dynamic Named ranges. There are some examples he
http://www.ozgrid.com/Excel/DynamicRanges.htm

Hope this helps,
Miguel.

"Purnima Sharma" wrote:

I have used sumproduct in my worksheet. It looks like following:
=SUMPRODUCT(($P$3:$P$7734="P1 Howard") * ($H$3:$H$7734=DATEVALUE("5/1/2006")))

It works fine. I have to use this formula for various dates and teams. The
report using this formula is generated everyday. I have column "A" showing
Loan numbers which is the only column that can not be null. Is there any
formula I can put to replace the range. The range is not known and changes
everyday. I want it to go to the end of file (based on column A). That way I
don't have to change the range manually and report can be updated dynamically
everyday. Thanks for your help.

Purnima Sharma

sumproduct won't let you leave out the rows, but there's nothing to prevent
you from calculating based on all rows, most of which happen to be empty:
=SUMPRODUCT(($P$3:$P$65536="P1 Howard") *
($H$3:$H$65536=DATEVALUE("5/1/2006")))

"Purnima Sharma" wrote:

I have used sumproduct in my worksheet. It looks like following:
=SUMPRODUCT(($P$3:$P$7734="P1 Howard") * ($H$3:$H$7734=DATEVALUE("5/1/2006")))

It works fine. I have to use this formula for various dates and teams. The
report using this formula is generated everyday. I have column "A" showing
Loan numbers which is the only column that can not be null. Is there any
formula I can put to replace the range. The range is not known and changes
everyday. I want it to go to the end of file (based on column A). That way I
don't have to change the range manually and report can be updated dynamically
everyday. Thanks for your help.

Purnima Sharma

Just set the last cell to some row you will never exceed.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Purnima Sharma" wrote in message
...
I have used sumproduct in my worksheet. It looks like following:
=SUMPRODUCT(($P$3:$P$7734="P1 Howard") *
($H$3:$H$7734=DATEVALUE("5/1/2006")))

It works fine. I have to use this formula for various dates and teams. The
report using this formula is generated everyday. I have column "A"
showing
Loan numbers which is the only column that can not be null. Is there any
formula I can put to replace the range. The range is not known and changes
everyday. I want it to go to the end of file (based on column A). That way
I
don't have to change the range manually and report can be updated
dynamically
everyday. Thanks for your help.

Purnima Sharma