I am having a problem trying to set up this formula. I have a range of cells, with columns for each period of a game, and rows for each game, with the scores in the range. For a certain player, I want to be able to input in a different row what game they played, and what period they came in and out of the rotation, and have it total up the score.
Period
Game
1 2 3 4 5 6 7 8 9
1
0 3 2 4 0 0 0 0 0
2
0 1 1 5 5 3 0 0 0
3
0 2 4 0 0 0 0 0 0
4
0 5 5 5 0 0 0 0 0
5
0 3 2 2 4 0 0 0 0
6
0 4 4 2 0 0 0 0 0

Player Game Period IN Period OUT Score
Adam 1 2 4


For example, here, with Adam, he played in Game 1, started playing in the 2nd period, and left in the 4th. I would want the score accumulated while he was on the field (in this example it would be 9... Game 1 Period2+Game1Period 3+Game1 Period4).

I am trying to work a formula that will automatically do that calculation when I input the game and period numbers down in the roster section. Any tips?

Thank you!!