Thread: sumproduct formula

I am building a spreadsheet to track items requested and the dates they return.
There are 2 categories of items tracked.
There a several sheets. 1 sheet is the log. 1 sheet contains all the
calculations that feed the reports on 12 individual sheets (monthly reports).
On the log I have a column that calculates the age of the outstanding items
(date requested to current date).
I need a formula for the reports that will give a count of the outstanding
items (no date received) categorized by their age (under/over 40 days old).

Hi,

Please see Bob Phillips' reply to you within the last two hours in this same
newsgroup. You posted your message twice here within a couple hours. Once
will suffice, and you ought to answer Bob's questions/requests.

Regards,
Kevin


"mbparks" <mbparks@discussions.microsoft.com> wrote in message
news:BDC8D74A-CBE2-4159-9E1C-C25187E6B09B@microsoft.com...
>I am building a spreadsheet to track items requested and the dates they
>return.
> There are 2 categories of items tracked.
> There a several sheets. 1 sheet is the log. 1 sheet contains all the
> calculations that feed the reports on 12 individual sheets (monthly
> reports).
> On the log I have a column that calculates the age of the outstanding
> items
> (date requested to current date).
> I need a formula for the reports that will give a count of the outstanding
> items (no date received) categorized by their age (under/over 40 days
> old).

Very sorry. My internet is acting strange this afternoon. I did not see my
question nor the response posted so I posted the question again.
Thanks.

"mbparks" wrote:

> I am building a spreadsheet to track items requested and the dates they return.
> There are 2 categories of items tracked.
> There a several sheets. 1 sheet is the log. 1 sheet contains all the
> calculations that feed the reports on 12 individual sheets (monthly reports).
> On the log I have a column that calculates the age of the outstanding items
> (date requested to current date).
> I need a formula for the reports that will give a count of the outstanding
> items (no date received) categorized by their age (under/over 40 days old).

here's the answer

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:9F575C5B-34A0-4E04-9352-DC6832AE2CDD@microsoft.com...
> Very sorry. My internet is acting strange this afternoon. I did not see
> my
> question nor the response posted so I posted the question again.
> Thanks.
>
> "mbparks" wrote:
>
>> I am building a spreadsheet to track items requested and the dates they
>> return.
>> There are 2 categories of items tracked.
>> There a several sheets. 1 sheet is the log. 1 sheet contains all the
>> calculations that feed the reports on 12 individual sheets (monthly
>> reports).
>> On the log I have a column that calculates the age of the outstanding
>> items
>> (date requested to current date).
>> I need a formula for the reports that will give a count of the
>> outstanding
>> items (no date received) categorized by their age (under/over 40 days
>> old).

Hi

i responded to your original post, but i've included my answer here too just
in case you can't see your original post:

"mbparks" <mbparks@discussions.microsoft.com> wrote in message
news:9F575C5B-34A0-4E04-9352-DC6832AE2CDD@microsoft.com...
> Very sorry. My internet is acting strange this afternoon. I did not see
> my
> question nor the response posted so I posted the question again.
> Thanks.
>
> "mbparks" wrote:
>
>> I am building a spreadsheet to track items requested and the dates they
>> return.
>> There are 2 categories of items tracked.
>> There a several sheets. 1 sheet is the log. 1 sheet contains all the
>> calculations that feed the reports on 12 individual sheets (monthly
>> reports).
>> On the log I have a column that calculates the age of the outstanding
>> items
>> (date requested to current date).
>> I need a formula for the reports that will give a count of the
>> outstanding
>> items (no date received) categorized by their age (under/over 40 days
>> old).