Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Two dimensional sum by week
Hi,
I've got a problem relating to summing daily totals, by an individual, by week. Here's my source data: Date Name Hrs 21/02/2005 John 11 22/02/2005 John 9 23/02/2005 George 7 23/02/2005 Fred 7.5 24/02/2005 John 9 24/02/2005 Fred 7.5 etc. What I'd like to produce is a table something like this: Week Commencing 21/2 28/2 7/3 John George Fred And at the intersect, have the total hours worked during that week. I guess some form of two dimensional SUMIF? Any ideas? Thanks, Jon C |
#2
|
|||
|
|||
Hi!
Assume your current table is in sheet1 A1:C7. To create your new table: Assume you want it on sheet2. List the employee names in sheet2 A2 on down. I'd use an Advanced Filter to copy the unique values. In sheet2 A1 enter: Week Commencing In sheet2 B1 enter: 2/21/2005 In sheet2 C1 enter: =B1+7 Copy across as needed In sheet2 B2 enter this formula: =SUMPRODUCT(--(Sheet1!$B$2:$B$7=$A2),--(Sheet1! $A$2:$A$7=B$1),--(Sheet1!$A$2:$A$7<B$1+7),Sheet1! $C$2:$C$7) Copy across then down as needed. Adjust references to suit. You may have to tweak things once you reach the end of the year/beginning of the next year depending on how you want to handle it. Biff -----Original Message----- Hi, I've got a problem relating to summing daily totals, by an individual, by week. Here's my source data: Date Name Hrs 21/02/2005 John 11 22/02/2005 John 9 23/02/2005 George 7 23/02/2005 Fred 7.5 24/02/2005 John 9 24/02/2005 Fred 7.5 etc. What I'd like to produce is a table something like this: Week Commencing 21/2 28/2 7/3 John George Fred And at the intersect, have the total hours worked during that week. I guess some form of two dimensional SUMIF? Any ideas? Thanks, Jon C . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to get 52 week hi/lo data for UK stocks | Excel Worksheet Functions | |||
Line or bar graphs for tracking stocks profit and loss. | Charts and Charting in Excel | |||
I need week number in excell from a date, first week must be mini. | Excel Discussion (Misc queries) | |||
Dynamic Column VlookUps Based on Week Number | Excel Worksheet Functions | |||
first week of year - according to european standard | Excel Worksheet Functions |