Running total with conditions

[PrincessColumbia]

So I'm putting together a spreadsheet for calculating hours for a paycheck and I've run into an issue generating a running total to calculate overtime.

Here's my columns:

• Date (One row per day worked)
• Week number
• Hours worked (this is where the data gets entered, everything else is calculated)
• Running total hours for the week
• Base hours
• Overtime hours
• Base rate (manually entered, but doesn't change much)
• Overtime rate (calculated based on Base Rate)
• Base pay
• Overtime pay
• Total pay

So the process I'm trying to do should be, "If the total hours so far for this week are more than 40, then take all the hours above 40 and put them in the overtime column. Otherwise, just add the time to the Base Hours column."

My major logjam on this is the Running Total Hours for the Week. The formula I'm attempting is this:

=SUMIFS(INDEX([Hours worked],1):[@[Hours Worked]]),[Week Number],[@[Week Number])

As written, it produces an error. If I just do the INDEX formula, it does a running total of the "Hours Worked" column no problem, but naturally that doesn't help me with just the week.

Any help?

 

[mart37]

=SUMIF([Week Number],[@Week Number],[Hours Worked])

 

[PrincessColumbia]

No dice, that just gives me the total of the week in every cell, not a running total.

 

[StephenCrump]

Perhaps something like this:

Base: =[@Hours]-[@Overtime]
Overtime: =MEDIAN(0,[@Hours],SUM(INDEX([Hours],MATCH([@WeekNo],[WeekNo],)):[@Hours])-40)

Excel 2010
	A	B	C	D	E
1	WeekNo	Day	Hours	Base	Overtime
2	1	09 Oct 2017	8.0	8.0	0
3	1	10 Oct 2017	12.5	12.5	0
4	1	11 Oct 2017	10.0	10.0	0
5	1	12 Oct 2017	9.0	9.0	0
6	1	13 Oct 2017	8.0	0.5	7.5
7	2	16 Oct 2017	8.0	8.0	0
8	2	17 Oct 2017	8.0	8.0	0
9	2	18 Oct 2017	15.0	15.0	0
10	2	19 Oct 2017	12.5	9.0	3.5
11	2	20 Oct 2017	8.0	0.0	8
Sheet1

 

[mart37]

running total: =SUMIF(INDEX([Week Number],1):[@Week Number],[@Week Number],INDEX([Hours worked],1):[@Hours worked])

 

[PrincessColumbia]

That did the trick! Thanks much!

 

[PrincessColumbia]

BTW, your suggestion worked excellently as well!