Sum only up to a certain point, then reset

[samc2696]

I have a list of staff with the hours they have worked on a weekly basis. I want to be able to put in a week's column when a payment has been made (this can be done sporadically and so isn't every week). When I put this in a column I then need a row below to reset for the following week to only show the hours that haven't been paid. For example in the below:

Acting Tracker - FINAL.xlsx
	I	J	K	L	M
2		02 Nov '19	09 Nov '19	16 Nov '19	23 Nov '19
3
4
5	Paid
6	Hrs worked	60:50	60:50	23:40	40:25
7	Sum of hrs	60:50	121:40	145:20	185:45
8	Sum of paid	60:50	121:40	145:20	185:45
Balances

At the moment no payments have been made hence the 'Sum of Paid' is showing exactly the same as the 'Sum of Hours'. If I were to put a Y (for paid) in say K6 i.e. payment of the 121:40 hrs worked till that point, then I would want L8=23:40 and M8=64:05 (23:40+40:25), e.g:

Acting Tracker - FINAL.xlsx
	I	J	K	L	M
2		02 Nov '19	09 Nov '19	16 Nov '19	23 Nov '19
3
4
5	Paid		Y
6	Hrs worked	60:50	60:50	23:40	40:25
7	Sum of hrs	60:50	121:40	145:20	185:45
8	Sum of paid	60:50	121:40	23:40	64:05
Balances

How can I get that to happen? I can't seem to get the SumIf and Index combination right.

Many thanks for any help

 

[kennypete]

These formulas should work for this. No SUMIFs nor INDEX should be required, just IF on K8..x8:

Book1
	I	J	K	L	M
2		2/11/2019	9/11/2019	16/11/2019	23/11/2019
3
4
5	Paid		Y
6	Hrs worked	60:50	60:50	23:40	40:25
7	Sum of hrs	60:50	121:40	145:20	185:45
8	Sum to pay	60:50	121:40	23:40	64:05
Sheet1

Cell Formulas
Range	Formula
K2:M2	K2	=J2+7
K7:M7	K7	=J7+K6
J8	J8	=J7
K8:M8	K8	=IF(J5="Y",K6,J8+K6)

 

[kennypete]

Hi @samc2696 did this solution sort it for you?