calculate the overtime of every employee for every area

Hello Everyone
-Sheet "Attendance contains the full timing of employees
-In this sheet"Report",every employee work in multiple areas
-I want to calculate the overtime of every employee for every area
Find the attachment

how do you decide area A or B?

Originally Posted by tim201110

how do you decide area A or B?

1- copy and paste wastes my time,because I have hundreds of employees.
2-don't take care of "area a or b", overtime depends on attendance
See the helping columns in " attendance " sheet ,and you will understand what I want

AG3=SUMPRODUCT((Attendance!$B$3:$B$31=B3)*(Attendance!$C$2:$AF$2-0>16)*(Attendance!$C$3:$AF$31))*24-SUMPRODUCT((Attendance!$B$3:$B$31=B3)*(Attendance!$C$2:$AF$2-0>16)*(Attendance!$C$3:$AF$31>0)*1)

AG13=SUMPRODUCT((Attendance!$B$3:$B$31=B13)*(Attendance!$C$2:$AF$2-0<17)*(Attendance!$C$3:$AF$31))*24-SUMPRODUCT((Attendance!$B$3:$B$31=B13)*(Attendance!$C$2:$AF$2-0<17)*(Attendance!$C$3:$AF$31>0)*1)

Originally Posted by CAABYYC

AG3=SUMPRODUCT((Attendance!$B$3:$B$31=B3)*(Attendance!$C$2:$AF$2-0>16)*(Attendance!$C$3:$AF$31))*24-SUMPRODUCT((Attendance!$B$3:$B$31=B3)*(Attendance!$C$2:$AF$2-0>16)*(Attendance!$C$3:$AF$31>0)*1)

AG13=SUMPRODUCT((Attendance!$B$3:$B$31=B13)*(Attendance!$C$2:$AF$2-0<17)*(Attendance!$C$3:$AF$31))*24-SUMPRODUCT((Attendance!$B$3:$B$31=B13)*(Attendance!$C$2:$AF$2-0<17)*(Attendance!$C$3:$AF$31>0)*1)

Thank you sir for your reply
1-Your formula gives me wrong results
2-I think your formula needs editing for every employee,but I want a fixed formula for all employees

AG3 =SUMPRODUCT((Attendance!B$3:B$31=B3)*(C3:AF3<>"")*(Attendance!C$3:AF$31))*24-SUMPRODUCT((Attendance!B$3:B$31=B3)*(C3:AF3<>"")*(Attendance!C$3:AF$31>0))*8