I am looking for a formula that counts the number of rows between cells with a number 1 in

Question

I have the following table:

I have the following formula in column B4

=IF(A4="","",MATCH(TRUE,INDEX(ISNUMBER(A5:A1872),,),0))

The formula is counting the number of blank rows between the number 1 in column A.

The formula i want though is one that puts my numbers like in Column C ie a step down. The 4 in B12 should be in B16. The 2 in B16 should be in B18 an so on.

Can someone help. I have Excel 2021?

I do not have the DROP() function in Excel 2021

Answer 1

Based on the first answer provided by @DavidLeal, though simplifying things somewhat:

=LET(
    ζ,A1:A12,
    ξ,ROW(ζ),
    IF(ζ=1,1+DROP(FREQUENCY(IF(ζ="",ξ),IF(ζ<>"",ξ,0)),-1),"")
)

Answer 2

How about this:

=LET(a,A4:A1872,
     b,ROW(a)*a,
     m,MMULT(--(TOROW(b)<b)*(TOROW(b)<>0),SEQUENCE(ROWS(b),,1,0)),
     x,TOCOL(IF(ISERR(1/b),NA(),b),3),
IF(b,b-INDEX(x,m),""))

This extracts the row number of the previous found value from the current row. )That means that the first value would show 0.)

Answer 3

Assuming you are counting the number of blank lines plus one, as it is in the sample. The following works regardless of the starting value, it could be 1 or blank. It also works if the blank cell is created by a formula, for example: ="". This answer assumes there is no Excel version constraints as per the tags listed in the question (formula 1).

=LET(A, A1:A15, seq, SEQUENCE(ROWS(A)),
 freq, DROP(FREQUENCY(IF(A="",seq), IF(A<>"",seq)),-1),
 f,FILTER(seq,A<>""), MAP(seq,LAMBDA(s,XLOOKUP(s,f,freq+1,""))))

Here another way using SCAN to count blanks within each group, then call MAP to find the max count per group, i.e. where the count (c) is zero take the previous element. Treating the case when the first 1 is at the beginning, in such situation INDEX(c,-1) produces an error, so IFERROR returns 1 (formula 2):

=LET(A,A1:A15, seq,SEQUENCE(ROWS(A)), c,SCAN(0,A,LAMBDA(ac,x,IF(x="",ac+1,0))),
 MAP(seq,LAMBDA(s,IF(INDEX(c,s,1)=0,IFERROR(INDEX(c,IF(s=1,0,s)-1)+1,1),""))))

Here is the output for formula 1:

The main idea in formula 1 is using FREQUENCY to do the counts. Idea taken from here: Occurrence in an excel serie, answer from @JosWoolley and credit to him. We remove the last value of freq, since it is the last bin (open interval). We are going to have as many frequency values as 1's are in the input. Now using MAP over the index positions (seq) and using XLOOKUP to find the specific index positions (f, index positions of the 1's ) and to return the corresponding frequency (freq) plus 1. If the index position was not found, then it means it is a blank row, so we return an empty string.