Count the number of rows between two numbers

William53

New Member
Joined
Jul 8, 2017
Messages
37
Office Version
  1. 365
Platform
  1. Windows
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
BCDEFGHIJ
56DateOpponentType0-1516-3031-4546-6061-7576-90
5721-Nov-21Macarthur FCL1
5827-Nov-21CC MarinersL1
593-Dec-21Western SydneyL11
607-Dec-21Western UnitedFFA
6110-Dec-21Newcastle JetsL1111
6214-Dec-21Avondale FCFFA1
6319-Dec-21Sydney FCL11
641-Jan-22Adelaide UtdL1111
655-Jan-22M. CityFFA
6621-Jan-22Western UnitedL1
6729-Dec-21M. VictoryFFA13
686-Feb-22Macathur FCL1
699-Feb-22M. VictoryL
7012-Feb-22Adelaide UtdL1
7115-Feb-22Brisbane Roar L1
7219-Feb-22Sydney FCL1
739-Mar-22Newcastle JetsL11
7412-Mar-22Brisbane Roar L11
7518-Mar-22Newcastle JetsL121
Team
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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?
 
Upvote 0
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-1516-30
76
9
 
Upvote 0
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
BCDEFGHIJ
41Number of games between goal concessions
42
430-1516-3031-4546-6061-7576-90
44764102
45911211
46112
47001
48310
49622
5033
5100
520
531
54
55
56DateOpponentType0-1516-3031-4546-6061-7576-90
5744521Macarthur FCL1
5844527CC MarinersL1
5944533Western SydneyL11
6044537Western UnitedFFA
6144540Newcastle JetsL1111
6244544Avondale FCFFA1
6344549Sydney FCL11
6444562Adelaide UtdL1111
6544566M. CityFFA
6644582Western UnitedL1
6744559M. VictoryFFA13
6844598Macathur FCL1
6944601M. VictoryL
7044604Adelaide UtdL1
7144607Brisbane Roar L1
7244611Sydney FCL1
7344629Newcastle JetsL11
7444632Brisbane Roar L11
7544638Newcastle JetsL121
76
Sheet2
Cell Formulas
RangeFormula
E44:E45,J44:J51,I44:I53,H44:H49,G44:G45,F44E44=LET(rowdiffs,FILTER(ROW(E57:E75)-1,E57:E75<>"")-FILTER(ROW(E56:E75),E56:E75<>""),INDEX(rowdiffs,SEQUENCE(ROWS(rowdiffs)-1)))
Dynamic array formulas.
 
Upvote 0
Solution
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top