Forum Discussion
Index/Match or Something Else
I have a worksheet that will look something like this:
| Last Name | Worked Department | Hours |
| Employee 1 | Project 7 | 2 |
| Employee 1 | Project 4 | 3 |
| Employee 1 | Project 5 | 1 |
| Employee 1 | Project 2 | 2 |
| Employee 1 | Project 4 | 1.5 |
| Employee 1 | Project 1 | 2 |
| Employee 1 | Project 2 | 2 |
| Employee 1 | Project 3 | 1 |
| Employee 1 | Project 10 | 1.5 |
| Employee 1 | Project 10 | 3 |
| Employee 1 | Project 4 | 2 |
| Employee 2 | Project 5 | 2 |
| Employee 2 | Project 4 | 1 |
| Employee 2 | Project 1 | 2 |
| Employee 2 | Project 3 | 3 |
| Employee 2 | Project 2 | 2 |
| Employee 2 | Project 4 | 1 |
| Employee 2 | Project 4 | 3 |
| Employee 2 | Project 2 | 2 |
| Employee 2 | Project 3 | 1 |
| Employee 2 | Project 3 | 2 |
| Employee 2 | Project 7 | 8 |
| Employee 3 | Project 5 | 1.5 |
| Employee 3 | Project 5 | 2.5 |
| Employee 3 | Project 5 | 2 |
| Employee 3 | Project 1 | 1 |
| Employee 3 | Project 4 | 1 |
| Employee 3 | Project 3 | 1.5 |
| Employee 3 | Project 4 | 2.5 |
| Employee 3 | Project 4 | 2 |
This worksheet will show the hours worked for thirty employees by Project worked.
I will be creating another worksheet that will look something like this:
| Employee 1 | Employee 2 | Employee 3 | Totals | |
| Project 1 | - | |||
| Project 2 | ||||
| Project 3 | - | |||
| Project 4 | - | |||
| Project 5 | - | |||
| Project 6 | - | |||
| Project 7 | - | |||
| Project 8 | - | |||
| Project 9 | - | |||
| Project 10 | - |
I want to write formulas in this worksheet such that it will look at the first worksheet and populate the second worksheet with the hours charged by employee to each of the projects. In this case the final work sheet would look like this:
| Employee 1 | Employee 2 | Employee 3 | Totals | |
| Project 1 | 2.00 | 2.00 | 2.00 | 6.00 |
| Project 2 | 4.00 | 4.00 | ||
| Project 3 | 1.00 | 6.00 | 2.50 | 9.50 |
| Project 4 | 6.50 | 5.00 | 5.50 | 17.00 |
| Project 5 | 1.00 | 2.00 | 6.00 | 9.00 |
| Project 6 | - | - | - | - |
| Project 7 | 2.00 | 8.00 | - | 10.00 |
| Project 8 | - | - | - | - |
| Project 9 | - | - | - | - |
| Project 10 | - | - | - | - |
I believe that a pivot table could be used but I need to do additional calculations using this data that would not work in a pivot table. The second worksheet will be a template for future months use.
I would appreciate any help/suggestions.
In B2 on Sheet2:
=SUMIFS('Sheet1'!$C$2:$C$31, 'Sheet1'!$A$2:$A$31, B$1, 'Sheet1'!$B$2:$B$31, $A2)
where Sheet1 is the name of the sheet with the hours.
Fill to the right to column C, then down to row 11.
In E2:
=SUM(B2:D2)
Fill down to row 11.
In B2 on Sheet2:
=SUMIFS('Sheet1'!$C$2:$C$31, 'Sheet1'!$A$2:$A$31, B$1, 'Sheet1'!$B$2:$B$31, $A2)
where Sheet1 is the name of the sheet with the hours.
Fill to the right to column C, then down to row 11.
In E2:
=SUM(B2:D2)
Fill down to row 11.
- Thank you for your help. This was a much simpler solution than what I was trying to do.
