Need Help with formula to calculate "hours scheduled" based on employee's number

I have been trying to make this work and still have not found a solution to it.

I am attaching a draft schedule. What I am trying to accomplish is a formula to look at the cells with the employee numbers (i.e. 1, 2, 3, 4 ect..) and calculate all the hours scheduled above or below that number, and place the total hours scheduled in column U beside the appropriate employee number.

The lines in "Yellow" are schedules that begin in AM and the hours are above the employee's number.

The lines in "Gray" are schedules that begin in PM and the hours are below the employee's number.

Example: C7 has employee #1 and above in C6 and D6 it shows 9AM to 9PM for a total of 12 hours. I need to look for all cells that contain employee #1
(C7, O12, and I23) and calculate those hours scheduled in rows above or below "1", then put the total hours scheduled in Column U beside appropriate employee number shown in Column R (should be 21:00:00).

So far I have been only able to calculate total hours using Columns W through AD for each day, and calculate how many shifts the employee is scheduled for Column T.

Hope this makes sense.

Any help would be greatly appreciated.

Rachel

See attached file.

I used your helper table to add the employee numbers and used multiple SUMIFS to add up the hours in column U. This is one way you can do it.

Thank you so very much. I never thought of that.

Works great....

Rachel