Counting Consecutive Cells with value

[JulieO]

I use Excel 2007 and I'm trying to find code or a formula that would count the number of consecutive cells in a row from left to right until it there is 0, Below is an example of what I'm trying to do:

<TABLE style="WIDTH: 245pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=324><COLGROUP><COL style="WIDTH: 28pt" span=5 width=37><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1024" width=29><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1280" width=36><COL style="WIDTH: 28pt" span=2 width=37><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d8d8d8; WIDTH: 28pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=37>A1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>B1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>C1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>D1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>E1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 22pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=29>G1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 27pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=36>H1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>I1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; WIDTH: 28pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=37>Result</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>32</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>1232</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>2</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>32</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>32</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>1231</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>123</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>8</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d8d8d8; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>0</TD></TR></TBODY></TABLE>

Column J has the results.

 

[Aladin Akyurek]

You are a crazy genius.

Thank you!

You are welcome.

 

[falconer81]

Hello guys! Thanks for the answers so far, it's awesome! But yet have a problem.
I have other values, for example 0s or empty cells between the counting sequences cells. And if there is 0 beetween the counting cells, the 0 value zeroes the sequence, other values not.

F.e: 111"1 - Result: 4, but 11101 - Result: 3 and 1

Hope you'll understand!

So what's the formula for counting this kind of "consecutive" cells, or sequences?

Thanks!

Regards,

 

[Aladin Akyurek]

Hello guys! Thanks for the answers so far, it's awesome! But yet have a problem.
I have other values, for example 0s or empty cells between the counting sequences cells. And if there is 0 beetween the counting cells, the 0 value zeroes the sequence, other values not.

F.e: 111"1 - Result: 4, but 11101 - Result: 3 and 1

Hope you'll understand!

So what's the formula for counting this kind of "consecutive" cells, or sequences?[
[...]

Are 111"1 and 11101 each in a single cell or in a range of cells?

 

[falconer81]

Are 111"1 and 11101 each in a single cell or in a range of cells?

In a range of cells. For example: from a1:j1: 1,"","","",1,1,0,"",1,1. For me the counted results: two 1s sequences: 3 and 2. Cause 0 breaks or separates the sequences, but "" not.

 

[Aladin Akyurek]

In a range of cells. For example: from a1:j1: 1,"","","",1,1,0,"",1,1. For me the counted results: two 1s sequences: 3 and 2. Cause 0 breaks or separates the sequences, but "" not.

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	1​				1​	1​	0​		1​	1​
2​
3​	2​
4​	list seq lengths
5​	3​
6​	2​
7​

In A3 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(A1:J1=0),COLUMN(A1:J1)),
    IF(ISNUMBER(A1:J1),IF(A1:J1=0,COLUMN(A1:J1)))),1))

A4: List seq lengths

In A5 control+shift+enter, not just enter, and copy down:

=IF(ROWS($F$5:F5)<=$A$3,INDEX(FREQUENCY(IF(1-($A$1:$J$1=0),COLUMN($A$1:$J$1)),
    IF(ISNUMBER($A$1:$J$1),IF($A$1:$J$1=0,COLUMN($A$1:$J$1)))),ROWS($F$5:F5)),"")

 

[falconer81]

Many thanks! Works perfectly!

If I want it in one formula, simply copy formula from a3 to a5 formula (part a3)?

 

[Aladin Akyurek]

Many thanks! Works perfectly!

If I want it in one formula, simply copy formula from a3 to a5 formula (part a3)?

Yes or in A5 control+shift+enter and copy down:

=IFERROR(INDEX(FREQUENCY(IF(1-($A$1:$J$1=0),COLUMN($A$1:$J$1)),
    IF(ISNUMBER($A$1:$J$1),IF($A$1:$J$1=0,COLUMN($A$1:$J$1)))),ROWS($F$5:F5)),"")

 

[falconer81]

Ok, thanks so much! Two more questions: In this range i look for the max sequence ( in this case:3) and the last one (from left to right) (in this case:2).

 

[Aladin Akyurek]

Ok, thanks so much! Two more questions: In this range i look for the max sequence ( in this case:3) and the last one (from left to right) (in this case:2).

Control+shift+enter:

=MAX(FREQUENCY(IF(1-($A$1:$J$1=0),COLUMN($A$1:$J$1)),
    IF(ISNUMBER($A$1:$J$1),IF($A$1:$J$1=0,COLUMN($A$1:$J$1)))))

gives you the longest sequence of 1's.

 

[falconer81]

Hello Aladin! Thanks for your formulas. Unfortunately it couldn't fit to my problem, cause the range is from other array formulas (and just a single a1:a20 form) and I couldn't make it to work.
I send an example sheet to present my question and problem.
I only make a solution with helper columns but I would like to find an elegant one formulas' solution.

Thanks in advance!

Link: Mega File Upload - seqexample.xlsx