PaulyK
Board Regular
- Joined
- Aug 27, 2015
- Messages
- 50
- Office Version
- 365
- Platform
- Windows
- MacOS
- Mobile
- Web
I have a spreadsheet containing Projects and Employee Resources. Each Month the allocated number of days per project to be worked are entered into the month column. I am looking for a way to be able to sum the amount of days worked, per project, per employee each month. I have used Sumifs to get a Total for the amount of days, per project for a specific month, but not with all the criteria (By Month, Employee, Project) to summarize in a separate worksheet. I can Sum the totals for a single Month column (Jan-22 in this case) by Project and Employee using the below formula. But how can I make it just use a specific month date? I have added an example below. It may be the case that there is a much easier approach - i only learnt SUMIFS recently!
SUMMARY sheet
I am using a Dynamic Range using =Sort(Unique for the projects so in the actual spreadsheet I am unable to create a table as it causes it to SPILL
| Project | Employee | Jan-22 | Feb-22 | Mar-22 | Apr-22 | May-22 | Jun-22 | Jul-22 | Aug-22 | Sep-22 | Oct-22 | Nov-22 | Dec-22 |
| Project 1 | Person 1 | 2 | 0 | 1 | 12 | 12 | 13 | 0 | 4 | 4 | 1 | ||
| Project 1 | Person 2 | 6 | 4 | 0 | 0 | 0 | 6 | 0 | 3 | 11 | 1.5 | ||
| Project 1 | Person 3 | 20 | 3 | 12 | 3 | 3 | 2 | 1 | 10 | 1 | 2 | ||
| Project 1 | Person 6 | 2 | 5 | 3 | 4 | 4 | 4 | 2 | 10 | 1 | 2.5 | ||
| Project 2 | Person 1 | 2 | 1 | 1 | 3 | 3 | 0 | 0 | 4 | 0 | 3 | ||
| Project 2 | Person 3 | 2 | 4 | 0 | 0 | 0 | 3 | 8 | 3.5 | ||||
| Project 3 | Person 3 | 0 | 8 | 11 | 19 | 0 | 0 | 0 | 4 | 3 | 4 | ||
| Project 3 | Person 3 | 4 | 6 | 4 | 6 | 4 | 6 | 4 | 6 | 4 | 4.5 | ||
| Project 4 | Person 2 | 12 | 2 | 8 | 10.5 | 2 | 4.5 | 3 | 5.5 | 5 | 5 | ||
| Project 5 | Person 1 | 1 | 0 | 0 | 8.5 | 0 | 0 | 10 | 0 | 6 | 5.5 | ||
| Project 6 | Person 7 | 1 | 0 | 0 | 2.5 | 0 | 10 | 7 | 7 | 6 | |||
| Project 7 | Person 1 | 1 | 0 | 0 | 3 | 0.5 | 0 | 4 | 0 | 0 | 6.5 | ||
| Project 7 | Person 5 | 20 | 20 | 21 | 18 | 3 | 3 | 3 | 3 | 0 | 7 | ||
| Project 7 | Person 6 | 3 | 0.5 | 12 | 1 | 1 | 6 | 4 | 10 | 10 | 21 | ||
| Project 8 | Person 1 | 2 | 0.5 | 0 | 0 | 0 | 0 | 0 | 0 | 8 | 22 | ||
| Project 9 | Person 3 | 3 | 3 | 3 | 3.5 | 0 | 0 | 0 | 0 | 3 | 0 | ||
| Project 9 | Person 6 | 0 | 7.5 | 10.5 | 18.5 | 1 | 2 | 5 | 6 | 0 | |||
| Project 9 | Person 7 | 0 | 1 | 2 | 5 | 7 | 8 | 0 | 1.5 | 7 | 0 | ||
| Project 9 | Person 8 | 5 | 5 | 5 | 10 | 0.5 | 0.5 | 1 | 0.5 | 9 | 0 | ||
| Project 10 | Person 1 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 1 | 1 | 0 | ||
| Project 11 | Person 4 | 2 | 3 | 4 | 5 | 5 | 12 | 5 | 0 | 2 | 1 | ||
| Project 12 | Person 4 | 3 | 1 | 4 | 6 | 9 | 1 | 4 | 9 | 3 | 2 | ||
| Project 13 | Person 2 | 0 | 1 | 5 | 4 | 3 | 5 | 4 | 3 | 4 | 1 | ||
| Project 14 | Person 5 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 1 | 2 | ||
| Project 15 | Person 8 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 0 | 8 | 0 | ||
SUMMARY sheet
| Book1 | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
| 1 | ID | Project | Person 1 | Person 2 | Person 3 | Person 4 | Person 5 | Person 6 | Person 7 | Person 8 | Person 9 | Person 10 | Month | Jan-22 | ||||
| 2 | Project 1 | 2 | Jan-22 | Feb-22 | ||||||||||||||
| 3 | Project 2 | 2 | Mar-22 | |||||||||||||||
| 4 | Project 3 | 0 | Apr-22 | |||||||||||||||
| 5 | Project 4 | 0 | May-22 | |||||||||||||||
| 6 | Project 5 | 1 | Jun-22 | |||||||||||||||
| 7 | Project 6 | 0 | Jul-22 | |||||||||||||||
| 8 | Project 7 | 1 | Aug-22 | |||||||||||||||
| 9 | Project 8 | 2 | Sep-22 | |||||||||||||||
| 10 | Project 9 | 0 | Oct-22 | |||||||||||||||
| 11 | Project 10 | 6 | Nov-22 | |||||||||||||||
| 12 | Project 11 | 0 | Dec-22 | |||||||||||||||
| 13 | Project 12 | 0 | Jan-23 | |||||||||||||||
| 14 | Project 13 | 0 | Feb-23 | |||||||||||||||
| 15 | Project 14 | 0 | Mar-23 | |||||||||||||||
| 16 | Project 15 | 0 | Apr-23 | |||||||||||||||
| 17 | May-23 | |||||||||||||||||
| 18 | Jun-23 | |||||||||||||||||
| 19 | Jul-23 | |||||||||||||||||
| 20 | Aug-23 | |||||||||||||||||
| 21 | Sep-23 | |||||||||||||||||
| 22 | Oct-23 | |||||||||||||||||
| 23 | Nov-23 | |||||||||||||||||
| 24 | Dec-23 | |||||||||||||||||
Summary | ||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:B16 | B2 | =UNIQUE(Table3[Project]) |
| C2:C16 | C2 | =SUMIFS(Table3[Jan-22],Table3[Project],Summary!B2,Table3[Employee],TblProjAlloc[[#Headers],[Person 1]]) |
| Dynamic array formulas. | ||
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| rngProject | =Summary!$B$2:$B$9 | C2 |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| N2 | List | =rngMonth |
I am using a Dynamic Range using =Sort(Unique for the projects so in the actual spreadsheet I am unable to create a table as it causes it to SPILL
