Sumproduct formula

[Guest]

I am working on a spreadsheet that tracks when items are requested and
received.
Sheet 1 is the log.
Sheet 2 is a formula sheet. (This is where I need another formula.)
I have 12 sheets- individual monthly reportsfor the year.
Can someone please help me with a formula that will count the number of
items outstanding and calculate the age of the outstanding items as of the
last day of the month for the reports.

 

[Bob Phillips]

I think it would help to have an idea of the data, what makes an item
outstanding, etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

An item is outstanding if it has not been returned.
Here is a general idea of the data:
Col H: type of request (859 or 281)
Col I: date requested
Col M: date returned
Col U: age of outstanding requests

 

[JulieD]

Hi

i would create a dynamic range name for column M
e.g.
returned
refers to
=OFFSET(Sheet1!$M$2,0,0,counta(Sheet1!$H:H$)-1,1)
(refer to http://www.contextures.com/xlNames01.html#Dynamic for details on
how to create dynamic range names)

then do a
=COUNTBLANK(returned)
formula to get the number of non-returned items

for the formula in column U i would use
=DATEDIF(I2,EOMONTH(NOW(),0),"m")

Cheers
julieD