1

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.

  • Welcome. Please add a brief description of you search/research efforts as is suggested on How to Ask. – Rubén Nov 7 '18 at 17:47
  • Thank you. I have just edited the top post with that information. – DWMARK Nov 7 '18 at 18:28
0

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.

0

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, then finds the first index of "1"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.