Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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). |
#2
|
|||
|
|||
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" wrote in message ... 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). |
#3
|
|||
|
|||
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). |
#4
|
|||
|
|||
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" wrote in message ... 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). |
#5
|
|||
|
|||
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" wrote in message ... 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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sumproduct formula | Excel Worksheet Functions | |||
sumproduct formula to slow | Excel Worksheet Functions | |||
SUMPRODUCT formula | Excel Worksheet Functions | |||
Shorten sumproduct formula | Excel Discussion (Misc queries) | |||
adding two sumproduct formulas together | Excel Worksheet Functions |