Help with Totals using VLOOKUP/SUMIF for employee timetables.

Hi All,

First post here so go gentle I have a project to redesign our store timetable to make it more efficient to timetable our staff. I have decided to go along the lines of Data Validation to enter in each employees shifts, using a table on a separate sheet (Shifts) with the different types of shifts in column A and the amount of hours each shift is in B. E.g, 9-5 in A1 8 in B1. The spreadsheet will have to have a different sheet for each week as I have done (Week x Week y etc.), as we need to keep a record and we also have a far amount of staff.

What I need is a formula to total up each employees hours per week at the end of the timetable in the column entitled Totals, whilst only displaying the data in column A of Shifts in the actual timetables. For example it will make it easy for Jack to see he is working 9-5 Monday and 12-4 Tuesday that week a total of 12 hours without displaying the 8 and 4 on the actual timetables associated with each of the shifts.

All my googling has led to VLOOKUP, which won't work I don't think, then some references to SUMIF as well, which I can't get to work. It's bugging me now as it is a pretty simple idea to just display one thing (the shift) but actually total up the hours column associated to each shift in the Shifts sheet, according to which shift the manager has selected using the Data Validation drop down .

Will try to attach the sheet, let me know if I have done it wrong....


I'm a basic user for now so basic explanations of what each formula you may suggest is actually doing would be great.

Thanks in advance,
Timetables.xlsx
Mike

UPDATE.

Just realised that the Data Validation didn't include the SICK HOL DUTY shifts etc, so have updated the validation to A:A rather than A6:...

Thank you for the reply, that's absolutely brilliant works a dream!

Really appreciate that!