excelexcel-formulaexcel-2021

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

I have the following table:

enter image description here

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

Solution

  • 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),"")
)