Roster calculations - trying to count number of weeks worked

[jimjimminy]

Hi there,

I have created a teaching roster for my staff and need to calculate the number of days and hours worked.

My requirements are:

1. Outlining the year's roster of teachers
2. Calculating the number of classes taught for each teacher (sheet 2, columns C-K)
3. Calculating the total hours taught (sheet 2, number of classes x total teaching hours - row 5)
4. Number of hours taught in a specific time period "Unsociable hours" (Each hour after 8.00pm Weekdays and all day Saturdays is worth 1.25 hours)
5. Total number of weeks taught

I believe I have achieved points 1-4 but I would be open to feedback if there is a better way.

For point 5, I'm not sure how to go about this. I'm wondering if there is a formula to count if a teacher appears once in a certain range. E.g. IF Teacher 1 appears in the range $F:$5:$K:$13, then count as 1 instance or one week worked.

Any assistance would be much appreciated.

Thank you.