Creating payroll for promotion that is based on two criteria

Question

We have a few coaches who work for us. When a session is booked, the data including the coach's name and student name is pushed to a master sheet.

We have criteria for promotion which is based on hours AND number of students coached. So if they reach a certain amount of hours but not the right amount of students, their hourly pay does not go up.

I am trying to figure out a way to automate payroll so that the sheet can calculate the total pay for that month but it needs to take into account the promotion criteria - so I am having a hard time figuring this out. I can pull in data of hours coached and unique students coached, but not sure how to get it to calculate the right pay based on the promotion criteria

For example: The first three hours are paid at $15 per hour and then the next 7 hours are paid at $20 per hour as long as the coach has coached more than three students. Any insight would be very helpful!

See above as explained. I can share the sheet if needed.

Answer 1

It's hard to show the desired result on the sheet. Basically lets say you have employee 1. They have worked 4 hours. The first three hours are paid at $15/hour and then after that it is paid at $20/hour, but they can only go up to $20/hour if they have seen more than 3 students Is there a formula something like "pay first three at 15, next 7 at 20, next at 25...etc?

=(MIN(C3, 3)*15) + 
 (MIN(MAX(C3-3, 0), 7)*IF(D3>=3, 20, 15)) + 
 (MAX(C3-10, 0)*IF(D3>=3, 25, 15))

---

update

in E9 use:

=VLOOKUP(C9, H3:K, IF(D9<=10, 3, IF((D9>10)*(D9<=25), 4, 2)), 0)

in E10 use this one and drag down:

=VLOOKUP(SUM(C$9:C10), H$3:K, IF(SUM(D$9:D10)<=10, 3, 
 IF((SUM(D$9:D10)>10)*(SUM(D$9:D10)<=25), 4, 2)), 0)-SUM(E$9:E9)