If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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$16=L2),--(F$2:F$1640),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 -- 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 |
Thread Tools | |
Display Modes | |
|
|