# excel sequence count

hi everyone,

i want to count the number of sequences of 1's in consecutive cells..eg 11100010011001, there are 4 sequences of ones here..can someone tell me what formula to use to do this in excel??

thanks
oggiemc
###### Who is Participating?

Commented:
if Richard is right then you could also just change the 1s in the formula to "1"s, i.e.

=SUMPRODUCT((A1:A14="1")*(OFFSET(A1:A14,1,0)<>"1"))

Regards, barry
0

Commented:
Assuming your data is in A1:A14 you can use this formula
=SUMPRODUCT((A1:A14=1)*(OFFSET(A1:A14,1,0)<>1))
regards, barry
0

Commented:
If your range is a horizontal range rather than a vertical one then the OFFSET part changes a little, e.g. if data range is A1:N1 then use
=SUMPRODUCT((A1:N1=1)*(OFFSET(A1:N1,0,1)<>1))
regards, barry
0

Author Commented:
Hi Barry,
My data is vertical..
that formula is just giving me back 0. Any ideas??

Runs      Runs of 1
0      0
1
1
0
1
0
0
0
0
0
1
0
1

0

Commented:
****No points****

If you select your column of 1s and 0s and go Data>TextToColumns>Finish does the formula now return a value?

If so it would indicate that you had text 1s/os rather than numeric 1s/0s

Richard
0

Author Commented:
thanks guys, it was text values..will split points :)
0

Commented:
Please don't split the points - your solution was all Barrie (and was a very clever solution I thought - much better than the one I came up with which was far too complicated to post).
0

Author Commented:
ok, will give to barry..thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.