Novice sumifs question

T

TheBigHalf

New Member
Joined
Jun 8, 2015
Messages
7
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
Sort by date Sort by votes
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))
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
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.
 
Upvote 0
TheBigHalf said:
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.
Click to expand...

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?
 
Upvote 0
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
 
Upvote 0
TheBigHalf said:
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
Click to expand...

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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Using formula to look up data where the data tab has the required data listed multiple times
Replies
1
Views
100
mumps
mumps
K
  • Question
AVERAGEIFS / SUMIF with multiple criteria
Replies
3
Views
219
arthurbr
arthurbr
J
  • Solved
HOW TO COMBINE SUMIF & INDIRECT FORMULA...sheets may or may not exist
Replies
6
Views
985
JuicyMusic
J
R
  • Question
Vlookup
Replies
4
Views
168
Robert Wyatt
R
N
  • Solved
Subtracting if drop down box says "Yes"
Replies
3
Views
2K
jtakw
jtakw

Forum statistics

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

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top