Count the number of rows between two numbers

[William53]

Dear All
I have the below data set and want to be able to count between games when the team concedes against the opposition, e.g., in the column 0-15 there is a count of 7 before the first goal conceded and then the second time is 9 before the next time a goal is conceded and so on down each column of time.
Is there a formula that can count the gaps?
I have tried searching frequency, count etc but I can only seem to find formulae that will reach the first count.

Many thanks

A League Scott Performance.xlsx
	B	C	D	E	F	G	H	I	J
56	Date	Opponent	Type	0-15	16-30	31-45	46-60	61-75	76-90
57	21-Nov-21	Macarthur FC	L					1
58	27-Nov-21	CC Mariners	L				1
59	3-Dec-21	Western Sydney	L					1	1
60	7-Dec-21	Western United	FFA
61	10-Dec-21	Newcastle Jets	L			1	1	1	1
62	14-Dec-21	Avondale FC	FFA					1
63	19-Dec-21	Sydney FC	L		1		1
64	1-Jan-22	Adelaide Utd	L	1			1	1	1
65	5-Jan-22	M. City	FFA
66	21-Jan-22	Western United	L						1
67	29-Dec-21	M. Victory	FFA					1	3
68	6-Feb-22	Macathur FC	L				1
69	9-Feb-22	M. Victory	L
70	12-Feb-22	Adelaide Utd	L						1
71	15-Feb-22	Brisbane Roar	L					1
72	19-Feb-22	Sydney FC	L					1
73	9-Mar-22	Newcastle Jets	L			1		1
74	12-Mar-22	Brisbane Roar	L	1					1
75	18-Mar-22	Newcastle Jets	L				1	2	1
Team

 

[KRice]

Depending on how you want to present the results, I would consider the XMATCH function, which gives you the option to search for "1" or any larger value and to search in a specific direction. For the example you described, where would you want 7 and 9 to appear?

 

[William53]

Hi KRice
Thanks for a quick response.
I would be placing the results in another data set under the same headings as the example

0-15	16-30
7	6
9

 

[KRice]

Here is one idea that constructs two arrays of the row numbers where a blank is not shown. Those two arrays aren't quite the same because the range specified in one of them includes the column header, so the first entry in that array will be the top of your column. Then by subtracting one array from the other, we'll obtain the number of rows between non-blanks (after making a -1 correction on one array). This will yield an array that contains the number of rows between successive non-blanks when moving from the top down. The last value in this result array is not meaningful and will be either an error or a negative number, so the last part of the formula counts the number of elements in the array and returns one fewer. The SPILL feature in Excel 365 will automatically spill the entire column of results downward, so you will need empty space below the formula to accommodate all of the results. Enter the formula once and drag across the relevant columns.

Book1
	B	C	D	E	F	G	H	I	J
41	Number of games between goal concessions
42
43				0-15	16-30	31-45	46-60	61-75	76-90
44				7	6	4	1	0	2
45				9		11	2	1	1
46							1	1	2
47							0	0	1
48							3	1	0
49							6	2	2
50								3	3
51								0	0
52								0
53								1
54
55
56	Date	Opponent	Type	0-15	16-30	31-45	46-60	61-75	76-90
57	44521	Macarthur FC	L					1
58	44527	CC Mariners	L				1
59	44533	Western Sydney	L					1	1
60	44537	Western United	FFA
61	44540	Newcastle Jets	L			1	1	1	1
62	44544	Avondale FC	FFA					1
63	44549	Sydney FC	L		1		1
64	44562	Adelaide Utd	L	1			1	1	1
65	44566	M. City	FFA
66	44582	Western United	L						1
67	44559	M. Victory	FFA					1	3
68	44598	Macathur FC	L				1
69	44601	M. Victory	L
70	44604	Adelaide Utd	L						1
71	44607	Brisbane Roar	L					1
72	44611	Sydney FC	L					1
73	44629	Newcastle Jets	L			1		1
74	44632	Brisbane Roar	L	1					1
75	44638	Newcastle Jets	L				1	2	1
76
Sheet2

Cell Formulas
Range	Formula
E44:E45,J44:J51,I44:I53,H44:H49,G44:G45,F44	E44	=LET(rowdiffs,FILTER(ROW(E57:E75)-1,E57:E75<>"")-FILTER(ROW(E56:E75),E56:E75<>""),INDEX(rowdiffs,SEQUENCE(ROWS(rowdiffs)-1)))
Dynamic array formulas.

 

[William53]

Hi KRice
This is brilliant, I never even knew there was a "rowdiffs". It works perfectly, thank you so much.
I have so much to learn.

 

[KRice]

I'm glad this works for you. There actually is not a "rowdiffs". Rather I used the LET function (a new function introduced in Excel 365) that allows one to define a name [rowdiffs] and a formula associated with that name [(FILTER(ROW(E57:E75)-1,E57:E75<>"")-FILTER(ROW(E56:E75),E56:E75<>"")]. Then the results generated by that name/formula can be called multiple times from another expression [INDEX(rowdiffs,SEQUENCE(ROWS(rowdiffs)-1))]. This makes the final formula a bit shorter and easier to understand because the messy formula associated with the name doesn't have to be repeated. In this case, rowdiffs gives the differences in row numbers, except that pesky last element in each rowdiffs array needs to be eliminated. So the final formula determines how many rows are in the array generated by rowdiffs (let's say that is "n"), subtracts one from that number (n-1), and feeds that smaller number to SEQUENCE to build a new array {1;2;3;...;n-1}...one element smaller than rowdiffs. And INDEX is then used to return the first n-1 elements of rowdiffs. Since rowdiffs needs to be called a couple of times, it made sense to define it just once with LET.

 

[William53]

Hi KRice
Thanks for the explanation, it will take me some time to digest what it means but once again, many thanks for your help.