Add up hours worked in a week.

[mrsbrannon]

Hello all,

I am trying to find a formula to add up how many hours an employee worked in each work week. There is lots of data on my sheet, but below are the columns that I believe will be pulled from. I'm fine with adding a helper column (shown in my example as the "total hours per week" column. Note, our work weeks are Friday to Saturday, so I need that factored in during the summing. It would also be nice if I can get the data to just appear once per name. Let me know if none of that makes sense.

Date	Employee Name	Hours Worked	Total Hours Per Week
6/23/2018	John Brown	10	60
6/24/2018	John Brown	10
6/25/2018	John Brown	10
6/26/2018	John Brown	10
6/27/2018	John Brown	10
6/28/2018	John Brown
6/29/2018	John Brown	10

<tbody>
</tbody>

 

[jorismoerings]

Hi,

Check if this works for you.

Book1
	A	B	C	D
1	Date	Employee Name	Hours Worked	Total Hours Per Week
2	06/23/2018	John Brown	10	60
3	06/24/2018	John Brown	10	0
4	06/25/2018	John Brown	10	0
5	06/26/2018	John Brown	10	0
6	06/27/2018	John Brown	10	0
7	06/28/2018	John Brown		0
8	06/29/2018	John Brown	10	0
Sheet1

Cell Formulas
Range	Formula
D2	=SUMPRODUCT(--(WEEKNUM($A$2:$A$8+0,16)=WEEKNUM(A2,16))*(SUMPRODUCT(--(WEEKNUM($A$2:$A2+0,16)=WEEKNUM($A2,16)))=1)*($C$2:$C$8))
D3	=SUMPRODUCT(--(WEEKNUM($A$2:$A$8+0,16)=WEEKNUM(A3,16))*(SUMPRODUCT(--(WEEKNUM($A$2:$A3+0,16)=WEEKNUM($A3,16)))=1)*($C$2:$C$8))
D4	=SUMPRODUCT(--(WEEKNUM($A$2:$A$8+0,16)=WEEKNUM(A4,16))*(SUMPRODUCT(--(WEEKNUM($A$2:$A4+0,16)=WEEKNUM($A4,16)))=1)*($C$2:$C$8))
D5	=SUMPRODUCT(--(WEEKNUM($A$2:$A$8+0,16)=WEEKNUM(A5,16))*(SUMPRODUCT(--(WEEKNUM($A$2:$A5+0,16)=WEEKNUM($A5,16)))=1)*($C$2:$C$8))
D6	=SUMPRODUCT(--(WEEKNUM($A$2:$A$8+0,16)=WEEKNUM(A6,16))*(SUMPRODUCT(--(WEEKNUM($A$2:$A6+0,16)=WEEKNUM($A6,16)))=1)*($C$2:$C$8))
D7	=SUMPRODUCT(--(WEEKNUM($A$2:$A$8+0,16)=WEEKNUM(A7,16))*(SUMPRODUCT(--(WEEKNUM($A$2:$A7+0,16)=WEEKNUM($A7,16)))=1)*($C$2:$C$8))
D8	=SUMPRODUCT(--(WEEKNUM($A$2:$A$8+0,16)=WEEKNUM(A8,16))*(SUMPRODUCT(--(WEEKNUM($A$2:$A8+0,16)=WEEKNUM($A8,16)))=1)*($C$2:$C$8))

 

[mrsbrannon]

It worked for one employee. But I have about 40 and when I copied the formula down, it didn't work for the rest. The layout is actually a table with headers as shown above. So I need the formula to apply to the entire table.

 

[mrsbrannon]

Thanks.

Cross-posted. There's a sample worksheet on the other one as well.

https://www.excelforum.com/excel-ge...orked-in-a-week-cross-posted-to-mr-excel.html

 

[jorismoerings]

Hi,

With the sample in mind, this is without using a helper column, try this:

Book1
	A	B	C	D
1	Date	Employee	Hours	Total
2	23-6-2018	Joe Brown	10	60
3	24-6-2018	Joe Brown	10	0
4	25-6-2018	Joe Brown	10	0
5	26-6-2018	Joe Brown	10	0
6	27-6-2018	Joe Brown	10	0
7	28-6-2018	Joe Brown		0
8	29-6-2018	Joe Brown	10	0
9	23-6-2018	Tom Jones	10	60
10	24-6-2018	Tom Jones	10	0
11	25-6-2018	Tom Jones	10	0
12	26-6-2018	Tom Jones	10	0
13	27-6-2018	Tom Jones	10	0
14	28-6-2018	Tom Jones		0
15	29-6-2018	Tom Jones	10	0
16	23-6-2018	Sally Smith	10	60
17	24-6-2018	Sally Smith	10	0
18	25-6-2018	Sally Smith	10	0
19	26-6-2018	Sally Smith	10	0
20	27-6-2018	Sally Smith	10	0
21	28-6-2018	Sally Smith		0
22	29-6-2018	Sally Smith	10	0
Sheet1

Cell Formulas
Range	Formula
D2	=SUMPRODUCT(--($B$2:$B$22=B2)*--(WEEKNUM($A$2:$A$22+0,16)=WEEKNUM(A2,16))*(SUMPRODUCT(--($B$2:$B2=B2)*(WEEKNUM($A$2:$A2+0,16)=WEEKNUM($A2,16)))=1)*($C$2:$C$22))