Payroll lookup

[Treasur2]

This is a classic payroll thing.

I two columns of names.

Coloumn L has a list of all names on the payroll.

Column D has a list of the same names, but mutliple times. Column E lists
the week. Column F lists each employees weekly hours.

Column O, I want to calcualte the total amount of hours upto 40 per week.

Column Q I want to total all hours that are over over 40.

Ex. John Smiths weekly hours
F13= 43
F14= 38
F15= 45
F16= 25

O14 = 143
Q14 = 8

 

[T. Valko]

Try this:

OT hrs (formula entered in Q2, put it where you want it but note that the
reg hrs formula will use this as reference):

=SUMPRODUCT(--(D$2$16=L2),--(F$2:F$16>40),F$2:F$16-40)

Reg hrs (this formula will reference the OT formula):

=SUMIF(D$2$16,L2,F$2:F$16)-Q2

Copy both formulas down as needed