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.