sumproduct

[Guest]

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

 

[Guest]

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

Hope this helps,
Miguel.

 

[Guest]

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")))

 

[Bob Phillips]

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)