# Novice sumifs question

#### TheBigHalf

##### New Member
Building a time sheet that logs time spent in different states and breaks down regular time vs overtime in each state.

Column A = State
Column B = Time in state
Column C = Running Total of time for the week

I am successfully using the following formulas to calculate regular time totals in one cell and overtime totals in another cell.

=SUMIFS(B6:B34,A6:A34,"MA",C6:C34,">40:00")
=SUMIF(A6:A34,"MA",B6:B34)-B37

Cell B37 being the result of the first formula

This works fine for everything before and after the log entry that actually crosses the 40hr threshold in which case the entire time value is added to the overtime total. I am stuck trying to figure out a way to split this value between the regular time and the overtime totals accordingly. Perhaps I am going about this all wrong ... like I said, I am a novice at best.

Any advice would be greatly appreciated.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Try
O/T (C37 is the 2nd formula)
=SUMIFS(B6:B34,A6:A34,"MA")-C37
Normal

=MIN("40:00",SUMIF(A6:A34,"MA",B6:B34))

Thanks for the quick reply but those formulas are not getting me the results I am looking for. I may not be explaining the situation properly. I imagine screen shots might make my issue easier to convey. I will work on posting some later on today. Thanks again.

I took it that you wanted to calc normal & O/T with normal being 40 anything above being O/T
e.g.
Code:
``````[TABLE="width: 195"]
<tbody>[TR]
[TD="width: 65"]MA[/TD]
[TD="class: xl63, width: 65, align: right"]3:00[/TD]
[TD="class: xl63, width: 65, align: right"]3:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]3:00[/TD]
[/TR]
[TR]
[TD]MA[/TD]
[TD="class: xl63, align: right"]8:00[/TD]
[TD="class: xl63, align: right"]11:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]11:00[/TD]
[/TR]
[TR]
[TD]MA[/TD]
[TD="class: xl63, align: right"]8:00[/TD]
[TD="class: xl63, align: right"]19:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]19:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]19:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]19:00[/TD]
[/TR]
[TR]
[TD]MA[/TD]
[TD="class: xl63, align: right"]12:00[/TD]
[TD="class: xl63, align: right"]31:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]31:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]31:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63, align: right"]31:00[/TD]
[/TR]
[TR]
[TD]MA[/TD]
[TD="class: xl63, align: right"]15:00[/TD]
[TD="class: xl63, align: right"]46:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64"]O/T[/TD]
[TD="class: xl64"]Normal[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63, align: right"]6:00[/TD]
[TD="class: xl63, align: right"]40:00[/TD]
[/TR]
</tbody>[/TABLE]``````

Yes and No. keep in mind I am working with multiple states and I am trying to show sub totals of reg & ot for each individual state. the formulas I am using do this correctly until the one entry that crosses the 40 hour threshold. For example let's say the first entry is for MA at 8 hours and the last entry is also for MA at 8 hours resulting in a total of 44 hours. all other entries are not MA. in this example the subtotal for MA regular would be 12 hours and OT at 4 hours. I assume I will need to include an additional function and/or another column of data to make this work.

I'm a little confused! Take the following example, what results would you expect for Normal & O/T for CA & MA?

Code:
``````[TABLE="width: 195"]
<!--StartFragment--> <colgroup><col width="65" span="3" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 65"]MA[/TD]
[TD="class: xl63, width: 65, align: right"]8:00[/TD]
[TD="class: xl63, width: 65, align: right"]8:00[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD="class: xl63, align: right"]4:00[/TD]
[TD="class: xl63, align: right"]12:00[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD="class: xl63, align: right"]4:00[/TD]
[TD="class: xl63, align: right"]16:00[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD="class: xl63, align: right"]8:00[/TD]
[TD="class: xl63, align: right"]24:00[/TD]
[/TR]
[TR]
[TD]MA[/TD]
[TD="class: xl63, align: right"]13:00[/TD]
[TD="class: xl63, align: right"]37:00[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD="class: xl63, align: right"]8:00[/TD]
[TD="class: xl63, align: right"]45:00[/TD]
[/TR]
[TR]
[TD]MA[/TD]
[TD="class: xl63, align: right"]4:00[/TD]
[TD="class: xl63, align: right"]49:00[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]``````

In that example I would expect MA Normal at 21, MA O/T at 4, CA Normal at 19 & CA O/T at 5. Your sixth entry is where my formula fails because the running total is greater then 40 therefore the entire eight hours is incorrectly added to the O/T sub total when really only the last five hours of the eight hour day would be considered overtime.

In that example I would expect MA Normal at 21, MA O/T at 4, CA Normal at 19 & CA O/T at 5. Your sixth entry is where my formula fails because the running total is greater then 40 therefore the entire eight hours is incorrectly added to the O/T sub total when really only the last five hours of the eight hour day would be considered overtime.

So if the 1st 6 total 40 hours and are in MA, then the last one is 6 hours in CA, MA would be 40 normal and CA 6 O/T?

Yes but again, the problem I'm having is when the 40 hour threshold is crossed mid day. If the 1st 6 total 39 and are in MA then the last one is 7 hours in CA, MA would be 39 normal and CA 1 normal and 6 O/T

Yes but again, the problem I'm having is when the 40 hour threshold is crossed mid day. If the 1st 6 total 39 and are in MA then the last one is 7 hours in CA, MA would be 39 normal and CA 1 normal and 6 O/T

Then I think it will need a helper column, like below

Code:
``````[TABLE="width: 260"]
<tbody>[TR]
[TD="width: 65"]MA[/TD]
[TD="class: xl63, width: 65, align: right"]8:00[/TD]
[TD="class: xl63, width: 65, align: right"]8:00[/TD]
[TD="class: xl63, width: 65, align: right"]0:00[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD="class: xl63, align: right"]8:00[/TD]
[TD="class: xl63, align: right"]16:00[/TD]
[TD="class: xl63, align: right"]0:00[/TD]
[/TR]
[TR]
[TD]NY[/TD]
[TD="class: xl63, align: right"]4:00[/TD]
[TD="class: xl63, align: right"]20:00[/TD]
[TD="class: xl63, align: right"]0:00[/TD]
[/TR]
[TR]
[TD]MA[/TD]
[TD="class: xl63, align: right"]8:00[/TD]
[TD="class: xl63, align: right"]28:00[/TD]
[TD="class: xl63, align: right"]0:00[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD="class: xl63, align: right"]13:00[/TD]
[TD="class: xl63, align: right"]41:00[/TD]
[TD="class: xl63, align: right"]1:00[/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD="class: xl63, align: right"]8:00[/TD]
[TD="class: xl63, align: right"]49:00[/TD]
[TD="class: xl63, align: right"]8:00[/TD]
[/TR]
[TR]
[TD]NY[/TD]
[TD="class: xl63, align: right"]5:00[/TD]
[TD="class: xl63, align: right"]54:00[/TD]
[TD="class: xl63, align: right"]5:00[/TD]
[/TR]
[TR]
[TD]MA[/TD]
[TD="class: xl63, align: right"]8:00[/TD]
[TD="class: xl63, align: right"]62:00[/TD]
[TD="class: xl63, align: right"]8:00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl63"][/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl64"]O/T[/TD]
[TD="class: xl64"]Normal[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]MA[/TD]
[TD="class: xl63, align: right"]8:00[/TD]
[TD="class: xl63, align: right"]16:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CA[/TD]
[TD="class: xl63, align: right"]9:00[/TD]
[TD="class: xl63, align: right"]20:00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]NY[/TD]
[TD="class: xl63, align: right"]5:00[/TD]
[TD="class: xl63, align: right"]4:00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]``````

My sample is in columns A - D
Formula in Helper column (D) is
=IF(C2>1.67,C2-"40:00",0)-SUM(\$D\$1:D1)
Formula for O/T

=SUMIFS(\$D\$1:\$D\$15,\$A\$1:\$A\$15,\$A18)

Replies
1
Views
100
Replies
3
Views
219
Replies
6
Views
985
Replies
4
Views
168
Replies
3
Views
2K

1,203,453
Messages
6,055,530
Members
444,794
Latest member
HSAL

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back