Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Stephen
 
Posts: n/a
Default Count the number of times 1 happened in a row

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.
--
Thanks!

Stephen
  #2   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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

--
Regards,

Peo Sjoblom


"Stephen" wrote in message
...
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.
--
Thanks!

Stephen



  #3   Report Post  
CLR
 
Posts: n/a
Default

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



"Stephen" wrote in message
...
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.
--
Thanks!

Stephen



  #4   Report Post  
Stephen
 
Posts: n/a
Default

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

"Peo Sjoblom" wrote:

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

--
Regards,

Peo Sjoblom


"Stephen" wrote in message
...
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.
--
Thanks!

Stephen




  #5   Report Post  
Stephen
 
Posts: n/a
Default

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" wrote:

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



"Stephen" wrote in message
...
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.
--
Thanks!

Stephen






  #6   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

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

--
Regards,

Peo Sjoblom


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

"Peo Sjoblom" wrote:

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

--
Regards,

Peo Sjoblom


"Stephen" wrote in message
...
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.
--
Thanks!

Stephen






  #7   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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))


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

"Peo Sjoblom" wrote:


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

--
Regards,

Peo Sjoblom


"Stephen" wrote in message
...

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.
--
Thanks!

Stephen




  #8   Report Post  
Stephen
 
Posts: n/a
Default

As I said, it doesn't work.


"Peo Sjoblom" wrote:

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

--
Regards,

Peo Sjoblom


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

"Peo Sjoblom" wrote:

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

--
Regards,

Peo Sjoblom


"Stephen" wrote in message
...
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.
--
Thanks!

Stephen






  #9   Report Post  
CLR
 
Posts: n/a
Default

=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" wrote in message
...
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" wrote:

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



"Stephen" wrote in message
...
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.
--
Thanks!

Stephen






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I count the number of times a particular charcter ("." say) RH Excel Discussion (Misc queries) 2 December 8th 06 03:21 AM
Number of times a number is shown David Excel Worksheet Functions 3 March 12th 05 12:30 PM
How do I count the times a number occurs in a given criteria? w_aller Excel Discussion (Misc queries) 2 February 3rd 05 10:06 AM
Count number to reach a cumulative value Bruce Excel Worksheet Functions 5 January 25th 05 06:14 PM
Count number of shaded cells Maddoktor Excel Discussion (Misc queries) 2 December 20th 04 09:35 PM


All times are GMT +1. The time now is 07:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"