IF formula impacted by previously entered data

Thanks in advance to all the Excel gurus out there.

I have attached a Data Sample file to walk through what we are trying to do ... if you look at the 'Summary' tab you will see some editable data attributes in cells J2:L6. Essentially what we are saying is that for tier 1 up to 500 hours the revenue accrual should be 25%, after 500 hours up to 1,000 hours the revenue accrual should be 10% and any hours greater than 1,000 the revenue accrual should be 5%. The issue I am running into is that we are looking to perform this calculation by month (see columns C-G on 'Summary' tab) and the data inputs stack over time.

For Jul-22 this math is easy: for contract 5000832870 I have 193 hours in Jul-22 (see 'PIVOT' tab); that falls below 500 hours so all 193 hours are allocated at 25% of revenue (193 hours ('PIVOT' cell E5 x rate per hour 'PIVOT' cell F5 x 25%). In Aug-22 I need to add 216 hours to 193 hours from July and I need to formula to confirm I'm still below 500 hours for the 25% allocation (or adjust to the new allocation % as needed).

As you can see the formula grows complex very quickly especially because the RPH changes by month as well.

I believe I'm on the right track with a nested IF formula, but also looking to see if there's an easier approach I'm not considering. I really appreciate all opinions and viewpoints - also happy to answer any additional questions.