Hi Rindy

There is no attachment.

Sorry.  Here is the attachment.

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.

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

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.