rota calculations

[eventhorse]

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

 

[smartin]

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

Generally speaking, conditional sums can be done with SUMIF (with one
criterion) or SUMPRODUCT (one or more criteria), but how these might fit
your case I cannot say as I cannot visualize your actual layout.

Here is an excellent resource for SUMPRODUCT in this context:
http://xldynamic.com/source/xld.SUMPRODUCT.html