Counting number of latest consecutive cells with specific criteria (Training Tracker)

Hi all

I have an Excel issue related to a training tracker, wondering if anyone can help advise?

1. There are Task A to H that trainees need to perform and they need to pass a consecutive of the latest 3 or 5 jobs in order to graduate from a certain task. Please refer to the Graduation benchmark table.
(For example, for a category that needs 5 passes, if the trainee passes 4 jobs consecutively but fails the last one, then the passing score needs to start from 0 again)

2. For Task A to C, trainees need to pass 3 jobs separately based on conditions 1 and 2.

3. On the tracker, each task will be marked as "N/A", "Fail" or "Pass". We can disregard the "N/A" since trainees will not perform that task in that job. Only when it's marked as "Fail" or "Pass", those cells will be taken into consideration.
(The cells that should be considered in the calculation are colored accordingly)

Ultimately, I would like to see the calculation be done in the performance review table, which is the sum of the latest consecutive passes.

Attached is the tracker template and please let me know if anything, thank you so much!

Hi AliGW, thanks for looking into this! Are you referring to job 18 row? Sorry that I'm not too sure which row you're referring to.
For the total passes, I'm looking at the sum of the column (e.g. task A/B/C) instead of the row (per job)
Attachment 785383

I tried to update the training tracker again, please let me know if anything unclear! Thanks again!
(I'm using Excel 2019)