Roster calculations - trying to count number of weeks worked

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.

For the number of weeks worked in January, please try in M6 and copy down:

Formula:

Please Login or Register  to view this content.

In M6 copied down. For me array entry required but not for you.

Please Login or Register  to view this content.

When same copied to next month to cell M17 Month reference is to be changed. Change $A$1 to $A$12.
Pl see file.

Thanks for your feedback. Glad to have helped.

Please consider adding reputation to all helpers, who you think they deserved because of the help provided.
If you want to, please click *Add reputation at the bottom left of their answers.

EDIT: Thanks for the rep .

My formula of Post #2 returns the correct values.
See attachment.

You are welcome.
Thanks for the feedback and rep

I thought this was a fun formula to make.