Formula for Calculating OT with Multiple Work Rates

[nella263]

Hi!

I need a formula that will help me do the following. I have spent hours trying to figure this out but haven't found a solution. Our employees work at multiple locations with different wages at every location. I need a formula that will take the summed total hours for all locations, apply their first 40 hours to their respective location, and add the remaining hours to an overtime column. I have attached a picture of my current excel sheet. Any help would be greatly appreciated! Thank you!

 

[AhoyNC]

You could use the FILTER function to bring in the hours to your cells B4 to B6. How are you handling the "Other" column?
=FILTER($B$18:$E$18,$B$10:$E$10=$A4)

Then the overtime hours could be calculated as:
=MAX(SUM(B4:B6)-40,0)