Calculate spare matches

[RichTheBowler]

[TABLE="width: 693"]
<tbody>[TR]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD]7[/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[TD]9[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]X[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]\[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]\[/TD]
[TD]8[/TD]
[TD]\[/TD]
[TD]9[/TD]
[TD]\[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]9[/TD]
[TD]\[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD="colspan: 2"]20[/TD]
[TD="colspan: 2"]40[/TD]
[TD="colspan: 2"]70[/TD]
[TD="colspan: 2"]99[/TD]
[TD="colspan: 2"]119[/TD]
[TD="colspan: 2"]137[/TD]
[TD="colspan: 2"]156[/TD]
[TD="colspan: 2"]176[/TD]
[TD="colspan: 2"]196[/TD]
[TD="colspan: 3"]216[/TD]
[/TR]
[TR]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"]"10"[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 2"]"10"[/TD]
[TD="colspan: 2"]"6 10"[/TD]
[TD="colspan: 2"]"4"[/TD]
[TD="colspan: 2"][/TD]
[TD="colspan: 3"]"10"[/TD]
[/TR]
</tbody>[/TABLE]

Line 1: Is lane numbers
Line 2: Is what was bowled in each frame
Line 3: Is the score attained in each frame
Line 4: Is the spare combination left after throwing the first ball

In this example: I left the "10" pin in frames 2,5, 10 and made all three spares.

Need formula to look at the value in line 4. Example: "10" and match with all spare values "the backslash" to count spares made by value "10" and subsequent values:
"6 10", "4", etc.

I tried =countifs, but does not work because the ranges in line 2 and 4 do not match, they are different sizes.

Thanks,
RichTheBowler[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

someone has produced a template, check it out

https://exceltemplate.net/tournament/bowling/