Jun 09 2021 05:39 AM
Hi,
I have what is essentially a daily time sheet for employee's. Each sheet is for a day of the week. In one row across the top, I have a series of codes in the format of x.xx.xx. In a column on the Y axis, I have a list of employee names. Essentially, each employee could have hours in any number of codes on the same day.
What I am trying to do is sum all of the hours worked for each individual code throughout the week in a different sheet.
The problem is that these codes and their locations in that top row change on a weekly basis. Essentially, I need something that can test each cell in that row to see what the code is, and then sum all of the hours underneath it.
I have attached a sample file below. Any help would be appreciated.
Thanks!
Jun 09 2021 12:11 PM
do you need to distinguish between RT, OT and DT, or do you just want to lump these together on the Total Hours sheet?
Jun 09 2021 12:27 PM
Jun 09 2021 12:45 PM
Solution
In B2 on the Total Hours sheet:
=IFERROR(SUM(OFFSET(Monday!$E$27:$AB$27,0,MATCH(A2,Monday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Tuesday!$E$27:$AB$27,0,MATCH(A2,Tuesday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Wednesday!$E$27:$AB$27,0,MATCH(A2,Wednesday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Thursday!$E$27:$AB$27,0,MATCH(A2,Thursday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Friday!$E$27:$AB$27,0,MATCH(A2,Friday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Saturday!$E$27:$AB$27,0,MATCH(A2,Saturday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Sunday!$E$27:$AB$27,0,MATCH(A2,Sunday!$E$5:$AB$5,0)-1,1,3)),0)
Fill down to B6.
Jun 09 2021 01:54 PM
Jun 09 2021 12:45 PM
Solution
In B2 on the Total Hours sheet:
=IFERROR(SUM(OFFSET(Monday!$E$27:$AB$27,0,MATCH(A2,Monday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Tuesday!$E$27:$AB$27,0,MATCH(A2,Tuesday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Wednesday!$E$27:$AB$27,0,MATCH(A2,Wednesday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Thursday!$E$27:$AB$27,0,MATCH(A2,Thursday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Friday!$E$27:$AB$27,0,MATCH(A2,Friday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Saturday!$E$27:$AB$27,0,MATCH(A2,Saturday!$E$5:$AB$5,0)-1,1,3)),0)+IFERROR(SUM(OFFSET(Sunday!$E$27:$AB$27,0,MATCH(A2,Sunday!$E$5:$AB$5,0)-1,1,3)),0)
Fill down to B6.