Reset "countif" after specific date is reached

[Rooz]

Hello all, hope all well.

Below is a little project I am working on as I have not been able to find an app that can solve my problem; I have a small dance school that charges 80 money for 8 classes, on an ongoing basis. Students attend same class, same time, each week (e.g. every Friday at 6PM). There are 2-3 classes given on any given day of the week, so each sheet will be related to a specific day and time.

The students pay for 8 classes in advance (marked in the sheet as “yes”), but if they let me know in advance that they cannot attend the class, then they do not need to pay for that class (excused). If it is a no-show, it counts as class was taken (noted as “no”). They can also chose to take the class on another day, which is noted as “comp” and counts. All of this is being easily counted in the sheet. I get a colour notification of when payment is due based on their pay date, =today, and sum of “=countif yes/no/comp”. So far all good and easy.

Here is what I have not been able to solve in an elegant way;

1. All is good and fine for the first 8 classes, the calculation lets me know when next payment is due based on simple formulas. But for next set of 8 classes (e.g. after first payment), it counts also the first “excused” that were linked to the first payment (formula counts the full line). How can I make it only count “excused” from the last “paid on” date (marked red), e.g. how can I “reset” “countif,21:21:”Excused”” to last payment date (cell Q20)? I would manually change the “paid on” date to (in this case to 13 Feb 2022), however how to I make the formula automatically exclude the two “excused” prior to that date (I do not want to delete them, I need to keep them, just in case asked in the future for attendance list)? I want all formula based as I cannot update the formula manually for each student, which also leads me into next question;

2. How do I duplicate this for 50 students in a smart way? At the moment it simply looks at the date and if any of the dates on line 20 = E20 then red, but I rather need the cell on the student line to turn red (e.g. student 2 should pay after 8 “yes”, so 30 Jan 2022 should turn red)

While I find it super fun working in excel and solving small problems like this, I have not been able to tackle these two yet.

Thanks in advance,
Roz