Thread: Sumproduct formula:ndividual monthly reportsfor

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" <mbparks@discussions.microsoft.com> wrote in message
news:FC8E5A01-B398-4812-8F2A-1BB0D8D7D571@microsoft.com...
> 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" <mbparks@discussions.microsoft.com> wrote in message
> news:FC8E5A01-B398-4812-8F2A-1BB0D8D7D571@microsoft.com...
> > 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" <mbparks@discussions.microsoft.com> wrote in message
news:9BB63FCD-5F9E-4169-9B86-AF7F3154C9E4@microsoft.com...
> 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" <mbparks@discussions.microsoft.com> wrote in message
>> news:FC8E5A01-B398-4812-8F2A-1BB0D8D7D571@microsoft.com...
>> > 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.
>>
>>
>>