I have a rota spreadsheet with dates across the top ( a week at atime)
and shift timings down the side ( for each day). The number of hours per
shift is calculated from the shift times and stored in a hidden column

Shift names are entered from a look up table to fill each available shift
according to a preset pattern. If no one available for shift vacant is
entered as a default. All this is colour coded not a figure in sight and
makes it visually easy for everyone to know what they are working each week.

My question is what formula (?vlookup and/or pivot table? ) can I use to
calculate for each weekly spreadsheet the total hours worked by each person.
I cannot get my head around the look up of hours is contingent on the row (
ie shift) they are working. Each person can work multiple shifts in the same
day, the shift pattern is different at weekends so the spreadsheet has
blanked out boxes where appropriate

Thanks
--
Mike