How to count current streak?

Question

I have a list of 1s and 0s in excel ranging from A1:A74 and I am looking to work out what the current streak of 1's is.

For example I have:

1
0
1
1
1

I would want the streak to give me 3.
I have tried the following formula which seems to work for smaller ranges, but for my full set it gives me the wrong amount:

=COUNTA(A1:A73)-MATCH(1,INDEX(1/(A1:A73=0),0))

Any help would be much appreciated.

edit - I believe i've fixed the formula above to work:

=COUNTA(S$2:S$74)-MATCH(2, 1/(S2:S$74=0), 1)

This is basically finding the last position of 0 and minusing this from the overall number of rows which have values.

Answer 1

If you can afford a helper column then there is another very easy way to do this.

Enter this formula in B2 (or any other cell in row 2(Assuming you are using a header)) and copy it down.

=IF(AND(A2=0,A3=0),B1+1,1)

You can then pick up the maximum value from this range and if required hide the column.

Answer 2

As Above, I've figured out the answer to my own question by just simplifying exactly what I needed to do and it became very obvious:

=COUNTA(S$2:S$74)-MATCH(2, 1/(S2:S$74=0), 1)

This is basically finding the last position of 0 and minusing this from the overall number of rows which have values.