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.

 

[Enrique Rod]

I was copying the formula the wrong way, so now it works perfectly!!
As for the second part, I was trying to add a conditional to identify if the sequence is more than 5, but I'm stuck here, not sure if it helps.

=SUM(--((FREQUENCY(IF(A5:S5>=1,COLUMN(A5:S5)),IF(A5:S5<1,COLUMN(A5:S5))))>=5))

 

[Peter_SSs]

I was copying the formula the wrong way, so now it works perfectly!!

Good news!

As for the second part, I was trying to add a conditional to identify if the sequence is more than 5, but I'm stuck here, not sure if it helps.

=SUM(--((FREQUENCY(IF(A5:S5>=1,COLUMN(A5:S5)),IF(A5:S5<1,COLUMN(A5:S5))))>=5))

That formula would count how many sequences 5 or longer but not how long those sequences are.

To record how long they are, try this copied across and down, assuming you have the TEXTJOIN function in your version of Excel.
Formula again confirmed with Ctrl+Shift+Enter before copying to the other cells.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
8																					First Sequence	Second Sequence	Third Sequence	X Sequence
9	1	1	1		1	1		1	1	1	1	1	1	1	1	1	1	1	1		12
10	1	1	1	1	1		1	1	1		1	1	1	1	1	1		1			5	6
Seq Count

 

[Enrique Rod]

It worked, the only issue is that where there is a sequence that does not meet the criteria (less than 5 consecutive cells), it shows the "#VALUE!" error.

 

[Peter_SSs]

It worked, the only issue is that where there is a sequence that does not meet the criteria (less than 5 consecutive cells), it shows the "#VALUE!" error.

Sorry, after testing I forgot to reintroduce the IFERROR()
{=IFERROR(MID(SUBSTITUTE(TEXTJOIN(" ",1,IF(FREQUENCY(IF($A9:$S9=1,COLUMN($A9:$S9)),IF($A9:$S9="",COLUMN($A9:$S9)))>4,FREQUENCY(IF($A9:$S9=1,COLUMN($A9:$S9)),IF($A9:$S9="",COLUMN($A9:$S9))),""))," ",REPT(" ",100)),COLUMNS($U:U)*100-99,100)+0,"")}

 

[Enrique Rod]

Sorry, after testing I forgot to reintroduce the IFERROR()
{=IFERROR(MID(SUBSTITUTE(TEXTJOIN(" ",1,IF(FREQUENCY(IF($A9:$S9=1,COLUMN($A9:$S9)),IF($A9:$S9="",COLUMN($A9:$S9)))>4,FREQUENCY(IF($A9:$S9=1,COLUMN($A9:$S9)),IF($A9:$S9="",COLUMN($A9:$S9))),""))," ",REPT(" ",100)),COLUMNS($U:U)*100-99,100)+0,"")}

Thank you so much Peter!!

 

[Peter_SSs]

Thank you so much Peter!!

You're welcome. Glad to help.

 

[Sachin111]

Welcome to the forums!

Try:

Excel 2007

	A	B	C	D	E	F	G	H	I
1	A1	B1	C1	D1	E1	F1	G1	H1	Result
2	32	0	0	123	123	0	1232	123	2
3	32	0	123	123	123	123	0	7	1
4	32	1231	123	123	123	123	8	7	0

<tbody>
</tbody>

Sheet1

<tbody>
</tbody>

Array Formulas Cell Formula I2 {=IFERROR(COLUMN(H2)-COLUMN(INDEX(A2:H2,,MATCH(9.99E+307,IF(A2:H2=0,1,"")))),0)} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hi All! Have been reading through the solutions provided, you all are geniuses. Thanks a lot for giving your time to us, its much appreciated!!

I have a similar problem which would require a little bit of modification to the quoted formula. Best to my knowledge, I believe your expertise would help me here.

Brief:
- The above data is calculating results from the range A2:H2 for example
- However, my data is being dynamically populated everyday, therefore my range today, for example is A2:J2, tomorrow it will be A2:K2
- Therefore, I am looking for a range reference to the last filled cell in a range of cells
- Rest of the conditions stays the same, ie, counting the last consecutive entries till it hits a zero.

I'll be really thankful for a solution. Hopefull.

 

[Sachin111]

Additional note:

Instead of 0s my condition to stop would be something else. This may drastically change the premise of the whole solution, but here it goes:

Suppose the Range A2:F2 has the following data today:
Yes No Yes Yes No Yes - The answer should be 1

Tomorrow if it is A2:G2:
Yes No Yes Yes No Yes Yes - The answer should be 2

Day after it is A2:H2:
Yes No Yes Yes No Yes Yes Yes - The answer should be 3

Day after that is A2:I2:
Yes No Yes Yes No Yes Yes Yes No - The answer should be 1

Day after that is A2:J2:
Yes No Yes Yes No Yes Yes Yes No No - The answer should be 2

Day after that is A2:K2:
Yes No Yes Yes No Yes Yes Yes No No Yes - The answer should be 1

Therefore, what is happening is, instead of 0s, it is taking into account the last value, seeing if it matches the previous value, if it is, rollup and count till a different value comes.

Additional note:
The formula would not be pasted in any cell in the range A2:N, it will be pasted in a cell for example, B3.

 

[Sachin111]

Final Addition, please excuse me for multiple add ons ray:

- Sum of Yes(s) should be in positives
- Sum of No(s) should be in negatives.

For example:
Day after it is A2:H2:
Yes No Yes Yes No Yes Yes Yes - The answer should be 3

Day after that is A2:J2:
Yes No Yes Yes No Yes Yes Yes No No - The answer should be -2

 

[Peter_SSs]

Welcome to the MrExcel board!

Her are 2 options that I think do what you want. The formulas can be placed wherever you want. In my example, they are each copied down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
2	Yes	No	Yes	Yes	No	Yes
3	Yes	No	Yes	Yes	No	Yes	Yes
4	Yes	No	Yes	Yes	No	Yes	Yes	Yes
5	Yes	No	Yes	Yes	No	Yes	Yes	Yes	No
6	Yes	No	Yes	Yes	No	Yes	Yes	Yes	No	No
7	Yes	No	Yes	Yes	No	Yes	Yes	Yes	No	No	Yes
8
9	1	1
10	2	2
11	3	3
12	-1	-1
13	-2	-2
14	1	1
Count Last Seq