Stuck on offset counting if conditions met

[rangelsammon]

Any help greatly appreciated!

Book2.xlsx

in the game of pool a player can play a "safety" which can usually will give a difficult shot to the opponent which can help the player return to the table. innings change when a players turn changes after miss shot. I am trying to count the average number of shots by the opponent after a safety

for example in rack 2 the answer is 3. 3 after johns safety highlighted. I am trying to do this for the entire match. I think I would be able to do add a contional column and add numbers up that way but im trying to avoid. any help greatly appreciated!

[#2]

@rangelsammon 

I'm struggling to understand the result of 3.3.

I assume by "after John's safety is highlighted" that you mean the rows above that row based on the inning numbers.  Rick appears to have made 3 shots above that row (2 pockets and 1 miss).    Where does the 0.3 come from, if so?  

If a row where safety = "yes" is the starting point, what's the stopping point?    The subsequent row where safety = "yes"?  Do all shots count regardless of being missed or pocketed?  And should the average span across racks and/or matches vs resetting at those milestones?   

Dexter

[#3]

Hi! @DexterG_III l, thanks for offering to help again.

The “.” is actually a period. So the inning is 2. he shot 3 times with the inning 2. 

the inning changes after you miss or change of player.

[#4]

im looking at it more and i think sumproduct might resolve.

[#5]

i added a formula to show the count for rack 2 if there was one safety. but there can be multiple safetys in a rack. i want to have in the whole match though.
=COUNTIFS(data[safety],"",data[rack],2,data[inning],XLOOKUP("yes",data[safety],data[inning])+0.5)

[#6]

ya no worries @dexter. im going to use a conditional column to add up when conditions met. i dont think its possible to do without. i was thinking offset and within use height to be a formula to count . but not working