Count the number of times 1 happened in a row

S

Stephen

Hi Everyone. I'm trying to figure out how to do a formula for the number of
times 1 happened in a row starting from a certain date.
For example, from A1 I have 1,1,1,0,0,1,1,1,1,0,0 - I would like to get the
answer of 3 (as starting from A1 I have 3 "1s" in a row, the rest of the
numbers aren't relevant). The max I want to be able to get is 11.
 
P

Peo Sjoblom

If the range will always start with 1 and you want to count from the first 1
you can use

=IF(COUNTIF(A1:A11,1)=COUNT(A1:A11),COUNT(A1:A11),MATCH(TRUE,A1:A11<>1,0)-MATCH(1,A1:A11,0))


entered with ctrl + shift & enter
 
C

CLR

Try this............

In L1 put 0
In M1 put =CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1)
In N1 put =MID(FIND(0,M1,1)-1,1,99)

Vaya con Dios,
Chuck, CABGx3
 
S

Stephen

Thank you.
I tried this formula and it returned #N/A. Do you know why this could be?

Peo Sjoblom said:
If the range will always start with 1 and you want to count from the first 1
you can use

=IF(COUNTIF(A1:A11,1)=COUNT(A1:A11),COUNT(A1:A11),MATCH(TRUE,A1:A11<>1,0)-MATCH(1,A1:A11,0))


entered with ctrl + shift & enter
Click to expand...
 
S

Stephen

Thank you.
I was hoping to put the formula all in one cell as I have so many formulas
all together. Do you know a way to do that?
Thanks!

CLR said:
Try this............

In L1 put 0
In M1 put =CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1)
In N1 put =MID(FIND(0,M1,1)-1,1,99)

Vaya con Dios,
Chuck, CABGx3
Click to expand...
 
P

Peo Sjoblom

As I said, you need to enter it with ctrl + shift & enter

--
Regards,

Peo Sjoblom


Stephen said:
Thank you.
I tried this formula and it returned #N/A. Do you know why this could be?
Click to expand...
 
A

Aladin Akyurek

Maybe, assuming no interspersed empty cells:

B1:

=MATCH(1,A1:A11,0)

B2:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},COUNTIF(A1:A11,1),MATCH(0,INDEX(A1:A11,B1+1):A11,0)+B1-B1))

Thank you.
I tried this formula and it returned #N/A. Do you know why this could be?

:
Click to expand...
 
C

CLR

=MID(FIND(0,CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1)&0,1)-1,1,99)

Vaya con Dios,
Chuck, CABGx3



Stephen said:
Thank you.
I was hoping to put the formula all in one cell as I have so many formulas
all together. Do you know a way to do that?
Thanks!
Click to expand...
 
Top