FDibbons is right about restructuring things. Your best bet is to adjust things so that you're entering numbers. However, if you really need to enter the shifts into columns D through J as they're currently constituted, then you should at least enter the number of hours for each shift in N19:N24, which would then allow you to do a straightforward sum of VLOOKUPs using the following formula in K12 (then fill down):
Formula:
=SUM(IFERROR(VLOOKUP($D12,$M$19:$N$24,2,FALSE),0),IFERROR(VLOOKUP($E12,$M$19:$N$24,2,FALSE),0),IFERROR(VLOOKUP($F12,$M$19:$N$24,2,FALSE),0),IFERROR(VLOOKUP($G12,$M$19:$N$24,2,FALSE),0),IFERROR(VLOOKUP($H2,$M$19:$N$24,2,FALSE),0),IFERROR(VLOOKUP($I12,$M$19:$N$24,2,FALSE),0),IFERROR(VLOOKUP($J12,$M$19:$N$24,2,FALSE),0))
Bookmarks