Google Sheet - calculating attendance from moving date range

Question

Good day folks, we have a Google Sheet with training attendance. We are meeting on weekly basis and calculate attendance from the past four months, last 36 month and historical total.

Situation: Currently, we calculate the attendance by assigning 2 (double training)/1 (normal training)/0 (canceled training) to the value of the training and attendee (2 on double, 1 on normal, 0 when absent) - and get attendance percentage by comparing the person's points sum to that of the trainings during the given period (4 months, 36 months, total).

Issue: The problem is that every time we add a training (new row in the tab), we need to manually change date range in the 4 month calculation and end date in the 36 months. I would love to automate this process that every time we add a new column, we would not have to change the date range.

Cells:

• C4 = maximum points available per trainings in the last 18 months
• D4 = maximum points available per trainings in the last 4 months
• C5 and below = user's attendance in the past 18 months (his points generated in that timespawn/total available points*100)
• D5 and below = user's attendance in the past 4 months (his points generated in that timespawn/total available points*100)
• E3:3 = cells with dates when we have trainings
• E4:4 = points awarded per that specific training
• E5:5 and below = points gained by the member per attendance

Question: is there any way count a number of training points gained in the past 4/18 months by a specific person?

Thank you kindly

Answer 1

try:

=SUM(FILTER(E5:5; E$3:$3>=TODAY()-120; E$3:$3<=TODAY()))/D$4