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" 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). |
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" 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). |
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). |
All times are GMT +1. The time now is 04:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com