Count the number of times 1 happened in a row

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
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...
 
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...
 
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...
 
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...
 
=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...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Count help with numbering Rows? 2
Get the row count of different areas in the Excel 2
counting starting with a number other than 1 1
Part 2 - Counting series of text/number sequence 3
Display individual number totals for rows 3
Help with Count formula 2
Counting number of days excluding certain values 3
counting a row of cells 3

Back
Top