Sumproduct formula
 

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.

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)


"mbparks" wrote in message
...
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.

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

"Bob Phillips" wrote:

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)


"mbparks" wrote in message
...
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.

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

"mbparks" wrote in message
...
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

"Bob Phillips" wrote:

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)


"mbparks" wrote in message
...
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.