Thanks for the write-up this option may work. Below are my reply on the queries posted and refinement require.

1.) The intermediate numbers in Row 8, Row 10 and Row 12 are require.

2.) When I changed the data in the attached file for Ivan and Susan as per below, row 8 result did not change correspondingly.

Ivan 23 15 0 15 22 0 55 40 10 21
Susan 15 0 15 30 0 15 10 75 13 12
R8 0 12 25 0 26 35 31 0 48 25

3.) Noticed that in row 7 the 1st data is preset as 1 is that the reason why the data do not change when the conditions are changed ?

4.) The data is larger than the example that can be expand accordingly once the formula is establish.