How to count the number of empty rows since the last entry in a column?

Question

I'm trying to get Google Sheets to tell me how many rows it has been since the last "1" was present in column A (see example table below). How do I get Google Sheets to output column B?

An alternative way of looking at this is to count the number of zeros/blanks in between the 1's in column A (then +1 to account for the row with the second 1) and print that number in Column B. How can I do this?

Data    # rows since last "1"
1
1       1
0
1       2
0
0
0
0
1       5
1       1

Search efforts: I have scoured the google products help forum, google and stackexchange for solutions but not found anything. The majority of answers I come up with involve counting the number of 1's or 0's/blanks, but none of the answers have allowed me to specifically count the number of rows between two ones.

I've also seen suggestions to use indexing to count the number of 1's or 0's within X rows above my current cell, but that wont work either because some 1's are sequential and sometimes it can be up to 60 rows of blanks between ones.

Now I'm at a loss for search terms for finding an answer that doesn't fall victim to the problems I've just described.

Answer 1

You know what, that is a question that seems super easy, but would be difficult with conventional formulas. Luckily, you can make your own formulas in Google Sheets.

Click, Tools > Script Editor then wait for it to pop up

paste the following code I have written for you,

function COUNTROWS(against, range) {
  range.reverse();
  for(var i = 1; i < range.length; i++) {
    if(range[i][0] == against) return i;
  }
  return -1;
}

Now you can use this in your formulas! If your sheet is set up the same way as your example, this would be the formula

=IF(A2=1, COUNTROWS(1, A$1:A2), "")

Note: This will only work after the first cell.

Answer 2

Alternative Answer Using Formulas

=IF(A2>0, FIND(1, ARRAYFORMULA(IFERROR(CONCATENATE(MID(CONCATENATE(A$1:A1), LEN(CONCATENATE(A$1:A1))-ROW(INDIRECT("1:"&LEN(CONCATENATE(A$1:A1))))+1,1)),""))),"")

This answer works by joining the cells above the current cell into a string, reversing it and then finding the first index of "1".