Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sum up the value in column based on a 5 day week period?
Hello,
Im trying to add the amount of time spent on a type of machinery based on a 5 day week period. For example from 3/1/2010 to 3/5/2010 not including weekends. On the sheet with the data of the amount of time I need added is layed out like this: Colomn A Colomn B Colomn K Example:3/1/2010 PL 1 1:54 3/1/2010 PL 5 0:13 3/1/2010 PL 1 0:07 3/2/2010 PL 5 0:13 3/2/2010 PL 1 1:00 3/2/2010 PL 5 0:30 and so on... On the sheet where the data is to be added and placed to populate my chart is layed out like this: The dates are based on the date of the ending week period. Colomn A Colomn B Colomn C Colomn D 3/5/2010 3/12/2010 3/19/2010 PL 1 PL 2 PL 3 PL 4 PL 5 How can i get the info to add up for a week without having to do use the SUMIF or SUMIFS based formula selecting the number of cells used for the 5 day week period at the end of each week? Let me know if the information I provided was enough to make my self clear. Your help is greatly appreciated. Thanks Peter |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to sum up the value in column based on a 5 day week period?
Hi Peter,
Suppose you have original data on sheet 1 and want to calculate 'chart data' on sheet 2, insert this formula in B2 of sheet2, and copy/paste the formula to fill the entire table: =SUMPRODUCT(--(Sheet1!$A$1:$A$100=Sheet2!B$1-5);--(Sheet1!$A$1:$A $100<=Sheet2!B$1);--(Sheet1!$B$1:$B$100=Sheet2!$A2);Sheet1!$K$1:$K $100) Regards, Per On 2 Mar., 19:42, Peter Gonzalez wrote: Hello, Im trying to add the amount of time spent on a type of machinery based on a * 5 day week period. For example from 3/1/2010 to 3/5/2010 not including weekends. On the sheet with the data of the amount of time I need added is layed out like this: * * * * * * *Colomn A * * * * *Colomn B * * * * * Colomn K Example:3/1/2010 * * * * * * *PL 1 * * * * * * * * * *1:54 * * * * * * *3/1/2010 * * * * * * *PL 5 * * * * * * * * * *0:13 * * * * * * *3/1/2010 * * * * * * *PL 1 * * * * * * * * * *0:07 * * * * * * *3/2/2010 * * * * * * *PL 5 * * * * * * * * * *0:13 * * * * * * *3/2/2010 * * * * * * *PL 1 * * * * * * * * * *1:00 * * * * * * *3/2/2010 * * * * * * *PL 5 * * * * * * * * * *0:30 and so on... On the sheet where the data is to be added and placed to populate my chart is layed out like this: The dates are based on the date of the ending week period. Colomn A * * * * * Colomn B * * * * Colomn C * * * * * Colomn D * * * * * * * * * * * * *3/5/2010 * * * * 3/12/2010 * * * * *3/19/2010 PL 1 PL 2 PL 3 PL 4 PL 5 How can i get the info to add up for a week without having to do use the SUMIF or SUMIFS based formula selecting the number of cells used for the 5 day week period at the end of each week? Let me know if the information I provided was enough to make my self clear. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number of hrs worked in a week, based on dates in a different column | Excel Worksheet Functions | |||
number of week days in a period of time | Excel Worksheet Functions | |||
Converting Dates to Week Ending time period | Excel Worksheet Functions | |||
Sum column based on value in each row, if two cells equal, or if date is within time period | Excel Worksheet Functions | |||
Dynamic Column VlookUps Based on Week Number | Excel Worksheet Functions |