Payroll sreadsheet

[RALPHEBOY398]

looking for help with formula for payroll sheet, not sure if isvalue or istext will help. We works shift work which consist of 17 hour one day and 7 the next morning. I was able to put a regular total hours example sheet attached which was 113 hours. what im trying to do is fill out colums T6,U6,V,X.Y.Z AA,AB based on on the difference from regular minus if employee also has acting, overtime, sick, vation or other.

Exmaple sheet i worked total 113 hour but 24 was vacation (V) and would like column AA show 24 but pulling if employee chooses V in row 5. It would be the same formula for example is A in row 5 it would be total of 89

Please let me know if you can help or add formula.

[Estevaoba]

Maybe this in cell S6 and copy across. Watch for merged cells in the way!

Please Login or Register  to view this content.

Good luck!

[RALPHEBOY398]

thanks would this change if i wanted on V or A or Even H for each field

[RALPHEBOY398]

that only gave me a zero value not 113

[Estevaoba]

You're welcome.
You get a zero in S6, because the SUMIF is adding up hours as per the letters in row 5. Since there's no R in range B5:R5, result is zero.
Make sure the titles in range S4:AC4 are the same as listed in A13:A20, so the VLOOKUP works properly.
Please check how it works in file attached.

[RALPHEBOY398]

thanks fro the help, it worked for R,A,H,

But did not add for AOT,S Acting
i highlight green (see excel rev.1)

Also tryng to understand formula

Regular formula
=SUMIF($B$5:$R$5,VLOOKUP(S$4,$A$13:$C$20,3,0),$B$6:$R$6)
Acting formula
=SUMIF($B$5:$R$5,VLOOKUP(T$4,$A$13:$C$20,3,0),$B$6:$R$6)

What part is formula is usinh C13 (regular hours) or C14 Acting (acting hours)

Thanks

[Estevaoba]

You're welcome. Thanks for the feedback.

The part of formula using C13, C14, etc, is the VLOOKUP.
As you have acronyms in range B5:R5 and the corresponding texts in range S4:AC4, I VLOOKUP your table in range A13:C20 to get the corresponding acronym into the SUMIF.
So, in the Acting formula =SUMIF($B$5:$R$5,VLOOKUP(T$4,$A$13:$C$20,3,0),$B$6:$R$6), the VLOOKUP(T$4,$A$13:$C$20,3,0) part stands for "A", the result of this VLOOKUP.
But, as I said in post #5, the titles in range S4:AC4 MUST be the same as listed in A13:A20.
Likewise, the acronyms in range B5:R5 MUST be the same as listed in range C13:C20.

The formula was not working for AOT and SOT because the texts were not the same in ranges S4:AC4 and A13:A20, so I made this correction.
Also, the S was returning zero, because in cell M5 it was "S", but in cell C20 it was "S " (with a space).
The Acting I in cell AC4 is not listed in your table A13:C20, so no result for it.

Please check file.