Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Payroll lookup
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Payroll lookup
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:D$16=L2),--(F$2:F$1640),F$2:F$16-40) Reg hrs (this formula will reference the OT formula): =SUMIF(D$2:D$16,L2,F$2:F$16)-Q2 Copy both formulas down as needed -- Biff Microsoft Excel MVP "Treasur2" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need payroll | Excel Discussion (Misc queries) | |||
how do i set up payroll | Excel Worksheet Functions | |||
Adding payroll stubs payroll calculator | New Users to Excel | |||
Payroll | New Users to Excel | |||
Payroll | Excel Discussion (Misc queries) |