Log In
Register
Home
New Member
December 21, 2019
Offline
I need to get a sum total that is dependent on another cell. In my example, I am totaling the number of "Standard" hours (up to a total of 40) and "Overtime" (over 40 hours) for each job number. A person may work on multiple jobs per day. We have a process in place to calculate this. No advice needed on this part.
More background:
1. I have tried to change the format. The bookkeeper is old and set in her ways. The format will not change.
2. We have 100's of potential job numbers so adding static cells won't be feasible. The line in which the job number is located will constantly be changing throughout the year.
The part that I am missing is the weekly total for each job number - the part highlighted in yellow. What formula needs to be put in the yellow cells in order to get the total I am looking for?
Thank you in advance for your help!
VIP
Trusted Members
June 25, 2016
Offline
Hi Rindy
There is no attachment.
New Member
December 21, 2019
Offline
Sorry. Here is the attachment.
Trusted Members
December 20, 2019
Offline
Strange spreadsheet!
The first 2 days in columns B to E dont have an overtime column, but the other 4 do - is this an error or by design?
I really dont like this formula but with the constraints you specify and the inablity to change anything it might suffice
=IF(SUMIFS($E$21:$E$36,$B$21:$B$36,$F40)+SUMIFS($N$21:$N$36,$K$21:$K$36,$F40)+SUMIFS($I$21:$I$36,$F$21:$F$36,$F40)+SUMIFS($E$3:$E$18,$B$3:$B$18,$F40)+SUMIFS($N$3:$N$18,$K$3:$K$18,$F40)+SUMIFS($I$3:$I$18,$F$3:$F$18,$F40)>40,40,SUMIFS($E$21:$E$36,$B$21:$B$36,$F40)+SUMIFS($N$21:$N$36,$K$21:$K$36,$F40)+SUMIFS($I$21:$I$36,$F$21:$F$36,$F40)+SUMIFS($E$3:$E$18,$B$3:$B$18,$F40)+SUMIFS($N$3:$N$18,$K$3:$K$18,$F40)+SUMIFS($I$3:$I$18,$F$3:$F$18,$F40))
Basically it is just 6 sumifs added together - 1 for each day. Then wrapped around an if statment to max out if the standard hours are greater than 40. I got stuck at this part as i wasnt sure where the overtime hours come from (the J & O columns, excess hours in columns E,I & N or a combination of both)
For the unique list of jobs, i would run a quick macro as i find it less complicated than a formula (click the button on the right)
Sub List()
Dim op As Range
Dim opCount As Integer
Dim ip1 As Range, ip2 As Range, ip3 As Range, ip4 As Range, ip5 As Range, ip6 As Range
With Range("t:t")
.ClearContents
.ClearFormats
End With
Set ip1 = Range("b3:b18")
Set ip2 = Range("b21:b36")
Set ip3 = Range("f3:f18")
Set ip4 = Range("f21:f36")
Set ip5 = Range("k3:k18")
Set ip6 = Range("k21:k36")
opCount = 1
'Set op = Range("T" & opCount)
ip1.Copy Range("t" & opCount)
ip2.Copy Range("t" & Cells(Rows.Count, "T").End(xlUp).Row + 1)
ip3.Copy Range("t" & Cells(Rows.Count, "T").End(xlUp).Row + 1)
ip4.Copy Range("t" & Cells(Rows.Count, "T").End(xlUp).Row + 1)
ip5.Copy Range("t" & Cells(Rows.Count, "T").End(xlUp).Row + 1)
ip6.Copy Range("t" & Cells(Rows.Count, "T").End(xlUp).Row + 1)
With Range("t:t")
.RemoveDuplicates Columns:=1, Header:=xlNo
.Sort key1:=Range("T:T"), order1:=xlAscending, Header:=xlNo
End With
End Sub
As i said i dont really like either of these, but if the template cant change it might work.
VIP
Trusted Members
June 25, 2016
Offline
Hi Rindy
I will just make the following assumption
1) There is a column F for Overtime, so I added one.
2) Maximum total "Standard" hours is 40 (I hope that is what you meant).
You can give this a try. In cell I40 (after adding an additional column F) enter
=MIN(SUMIFS(E$3:E$37,$B$3:$B$37,$G40)+SUMIFS(J$3:J$37,$G$3:$G$37,$G40)+SUMIFS(O$3:O$37,$L$3:$L$37,$G40),40)
The MIN formula will take the lower of the 2 values (i.e. max is 40).
Don't quite understand what you wanted for the Overtime so I don't have a solution yet.
Maybe you can give us your expected answers based on your attachment.
This will make it easier for us to understand what you are expecting the output to be.
Sunny
VIP
Trusted Members
December 7, 2016
Offline
Hello,
With the current layout you can't avoid messy formulas, as you probably have guessed by now. As also already mentioned, I assume you will have a column F showing overtime for the first two days. If you only need to sum the standard and overtime hours you can use SUMPRODUCT to do that for you, else I suggest you use any other given previous suggestion.
In cell I40 (as I assume you need to add an extra column F), paste in below formula, then drag and copy as needed.
=SUMPRODUCT(($B$3:$B$18=$G40)*(E$3:E$18)+($B$21:$B$36=$G40)*(E$21:E$36)+($G$3:$G$18=$G40)*(J$3:J$18)+($G$21:$G$36=$G40)*(J$21:J$36)+($L$3:$L$18=$G40)*(O$3:O$18)+($L$21:$L$36=$G40)*(O$21:O$36))
As your data is spread out the formula is a bit hard to read and understand, but the basics are as follows:
For any job number in cells B3 to B18 matching the job number in cell G40, sum the hours in cells E3 to E18, etc.
The + symbol equals OR and the * symbol equals AND.
As SUMPRODUCT is a bit more fussy about data format you can't just ask it for example to look in range B3 to B36, as that range contains both text and numbers.
I hope you will find a working solution.
1 Guest(s)
