Sum First intances based on 2 criteria

[cra5hdown]

the table below is just a small segment of a much larger table. the first instance per person each week is normal time, any other instances that week are overtime.

so what im looking to do is sum the total number of hours in normal time, and sum the total overtime per person (ill just take the total normal hours away from total hours per person for overtime total per person)

my sticking point is summing on the first instances per person per week

anyone got any ideas? thanks in advance

table:

date	person	hours
29/7/18	1	40.00
29/7/18	1	19.50
29/7/18	1	4.00
29/7/18	1	1.00
29/7/18	2	40.00
29/7/18	2	19.50
29/7/18	2	4.00
29/7/18	3	17.00
29/7/18	4	20.00
29/7/18	5	22.00
29/7/18	5	1.00
29/7/18	6	40.00
29/7/18	6	19.50
29/7/18	6	4.00
29/7/18	7	40.00
29/7/18	7	19.50
29/7/18	7	4.00
29/7/18	7	1.00
29/7/18	8	40.00
29/7/18	8	4.00
29/7/18	8	5.00

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[Eric W]

Maybe:

	A	B	C	D	E	F
1	date	person	hours		Regular hours	Overtime
2	29/7/18	1	40		259	106
3	29/7/18	1	19.5
4	29/7/18	1	4
5	29/7/18	1	1
6	29/7/18	2	40
7	29/7/18	2	19.5
8	29/7/18	2	4
9	29/7/18	3	17
10	29/7/18	4	20
11	29/7/18	5	22
12	29/7/18	5	1
13	29/7/18	6	40
14	29/7/18	6	19.5
15	29/7/18	6	4
16	29/7/18	7	40
17	29/7/18	7	19.5
18	29/7/18	7	4
19	29/7/18	7	1
20	29/7/18	8	40
21	29/7/18	8	4
22	29/7/18	8	5

<tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula E2 =SUMPRODUCT(C2:C22,--(MATCH(B2:B22,B2:B22,0)=ROW(B2:B22)-ROW(B2)+1)) F2 =SUM(C2:C22)-E2 <tbody> </tbody>

<tbody>
</tbody>

 

[cra5hdown]

Perhaps I wasnt clear with what i was after, the required output is:

Person 1 (hrs) (OT hrs)
Person 2 (hrs) (OT hrs)
Person 3 (hrs) (OT hrs)
etc
there are dozens of Persons, and ill have to do this regularly

there maybe two lines per person per date, the first line is normal hrs, the second line with the same date is that persons overtime

 

[Eric W]

I misunderstood, that's even easier:

	A	B	C	D	E	F	G	H	I	J	K
1	date	person	hours		Regular hours	Overtime		Person	Regular Hours	Overtime
2	29/7/18	1	40		259	106		1	40	24.5
3	29/7/18	1	19.5					2	40	23.5
4	29/7/18	1	4					3	17	0
5	29/7/18	1	1					4	20	0
6	29/7/18	2	40					5	22	1
7	29/7/18	2	19.5					6	40	23.5
8	29/7/18	2	4
9	29/7/18	3	17					7	40	24.5
10	29/7/18	4	20					8	40	9
11	29/7/18	5	22
12	29/7/18	5	1
13	29/7/18	6	40
14	29/7/18	6	19.5
15	29/7/18	6	4
16	29/7/18	7	40
17	29/7/18	7	19.5
18	29/7/18	7	4
19	29/7/18	7	1
20	29/7/18	8	40
21	29/7/18	8	4
22	29/7/18	8	5

<tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula I2 =VLOOKUP(H2,$B$2:$C$22,2,0) J2 =SUMIFS(C:C,B:B,H2)-I2 <tbody> </tbody>

<tbody>
</tbody>