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.

 

[Ingolf]

.

 

[Boogieman]

Hi! My question is very similar to bvyit's:
I want to count how many times a sequence of 3 number 1s takes place, but if sequence doesn't stop, it counts further as next sequence(if there is next sequence).
It can be empty cell instead of "0". Is it possible to count by formula?

*	A	B	C	D	E	F	G	H	I	J	K	L	M
1	A	B	C	D	E	F	G	H	I	J	K	L	Count a sequence of 3 number "1"
2	1	1	1	0	0	0	0	0	0	0	0	0	1
3	1	1	0	1	0	1	1	0	1	0	1	1	0
4	0	1	1	1	0	0	1	1	1	0	0	0	2
5	1	1	1	1	1	1	1	1	1	1	1	1	4
6	0	1	0	1	0	1	1	1	1	1	1	1	2
7	1	1	0	0	1	0	1	1	1	0	0	0	1

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:93px;"></colgroup><tbody>
</tbody>

<span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; "

</span>

 

[Aladin Akyurek]

Hi! My question is very similar to bvyit's:
I want to count how many times a sequence of 3 number 1s takes place, but if sequence doesn't stop, it counts further as next sequence(if there is next sequence).
It can be empty cell instead of "0". Is it possible to count by formula?

*	A	B	C	D	E	F	G	H	I	J	K	L	M
1	A	B	C	D	E	F	G	H	I	J	K	L	Count a sequence of 3 number "1"
2	1	1	1	0	0	0	0	0	0	0	0	0	1
3	1	1	0	1	0	1	1	0	1	0	1	1	0
4	0	1	1	1	0	0	1	1	1	0	0	0	2
5	1	1	1	1	1	1	1	1	1	1	1	1	4
6	0	1	0	1	0	1	1	1	1	1	1	1	2
7	1	1	0	0	1	0	1	1	1	0	0	0	1

<tbody>
</tbody>

<span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; "
</span>

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

=SUM(INT(FREQUENCY(IF($A2:$L2=1,COLUMN($A2:$L2)),IF(1-($A2:$L2=1),COLUMN($A2:$L2)))/3))

 

[Boogieman]

Aladin Akyurek
Awesome! Thanks a lot!

 

[Aladin Akyurek]

Aladin Akyurek
Awesome! Thanks a lot!

You are welcome.

 

[Ingolf]

Aladin you are earthly or alien???

 

[Aladin Akyurek]

Aladin you are earthly or alien???

You must have liked the set up!...

 

[lucashaley]

Okay, I have one more consecutive challenge. I have attendance sheet for students, and need to know if any student misses three consecutive classes in a row. So instead of checking for 1 or null, I need to check for "A" instead of "P" or "L". Any help greatly appreciated!

<table>
<tr>
<td>Student name</td><td>Monday</td><td>Tuesday</td><td>Wednesday</td><td>Thursday</td><td>Friday</td><td>Expected result</td>
<tr>
<td>Student A</td><td>P</td><td>A</td><td>A</td><td>A</td><td>P</td><td>3</td>
</tr>
<tr>
<td>Student B</td><td>P</td><td>P</td><td>A</td><td>P</td><td>A</td><td>1</td>
</tr>
</table>

-Lucas

 

[Aladin Akyurek]

Okay, I have one more consecutive challenge. I have attendance sheet for students, and need to know if any student misses three consecutive classes in a row. So instead of checking for 1 or null, I need to check for "A" instead of "P" or "L". Any help greatly appreciated!
[...]

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

=MAX(FREQUENCY(IF(B2:F2="A",COLUMN(B2:F2)),IF(1-(B2:F2="A"),COLUMN(B2:F2))))

 

[lucashaley]

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

=MAX(FREQUENCY(IF(B2:F2="A",COLUMN(B2:F2)),IF(1-(B2:F2="A"),COLUMN(B2:F2))))

You are a crazy genius.

Thank you!