Count consecutive 0’s and 1’s

[motilulla]

Using Excel 2010
Hello,

I have submitted example sheet below in the 20 columns C:V, I have table with 0’s and 1’s…I need a formula or VBA which can count consecutive 0’s and 1’s as shown in the columns Z:AS… if the count are correct column “AT” will sum 20...Please help.

Consecutive Question.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
1
2
3
4			z1	z2	z3	z4	z5	z6	z7	z8	z9	z10	z11	z12	z13	z14	z15	z16	z17	z18	z19	z20				Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Sum
5			z1	z2	z3	z4	z5	z6	z7	z8	z9	z10	z11	z12	z13	z14	z15	z16	z17	z18	z19	z20				0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	Sum
6			1	0	0	0	1	0	1	0	1	1	0	0	1	0	1	1	0	0	1	0					1	3	1	1	1	1	2	2	1	1	2	2	1	1						20
7			1	1	1	0	1	1	0	0	1	0	1	1	0	0	1	0	1	1	0	0					3	1	2	2	1	1	2	2	1	1	2	2								20
8			1	0	0	0	1	0	1	0	1	1	0	0	1	0	1	1	0	0	1	0					1	3	1	1	1	1	2	2	1	1	2	2	1	1						20
9			1	0	0	1	1	0	1	0	0	1	1	0	0	0	0	1	1	0	0	0					1	2	2	1	1	2	2	4	2	3										20
10			0	1	0	1	0	0	1	0	0	1	1	1	0	0	0	1	1	1	0	0				1	1	1	1	2	1	2	3	3	3	2										20
11			1	1	1	1	1	1	0	0	1	0	0	0	0	1	1	0	0	0	0	1					6	2	1	4	2	4	1													20
12			1	0	0	1	0	0	0	1	0	0	1	0	1	0	0	0	1	0	1	0					1	2	1	3	1	2	1	1	1	3	1	1	1	1						20
13			0	1	1	1	0	1	0	0	0	1	0	0	1	0	0	1	0	0	1	0				1	3	1	1	3	1	2	1	2	1	2	1	1								20
14			0	0	0	1	1	1	1	0	1	1	0	1	1	1	1	1	0	1	1	1				3	4	1	2	1	5	1	3													20
15			0	1	1	0	0	0	0	0	1	0	0	1	1	0	1	0	0	1	1	0				1	2	5	1	2	2	1	1	2	2	1										20
16			1	0	1	1	0	1	1	1	1	0	0	0	1	1	1	0	0	0	1	1					1	1	2	1	4	3	3	3	2											20
17			0	0	1	0	1	0	1	0	0	0	1	0	1	0	0	0	1	0	1	0				2	1	1	1	1	1	3	1	1	1	3	1	1	1	1						20
18			0	0	0	0	0	1	0	0	0	1	1	1	0	0	0	1	1	1	0	0				5	1	3	3	3	3	2														20
19			1	0	0	1	1	0	0	0	0	1	0	0	1	1	0	1	0	0	1	1					1	2	2	4	1	2	2	1	1	2	2									20
Sheet1

Cell Formulas
Range	Formula
AT6:AT19	AT6	=SUM(Z6:AS6)

Regards,
Moti

 

[KRice]

Due to posting size limitations, I'll show one idea in two posts. First construct some helper cells (see blue cells):

MrExcel_20240301.xlsx
	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO
6	1	3			1	1	1	1	2		2		1	1	2		2		1	1
7	3			1	2		2		1	1	2		2		1	1	2		2
8	1	3			1	1	1	1	2		2		1	1	2		2		1	1
9	1	2		2		1	1	2		2		4				2		3
10	1	1	1	1	2		1	2		3			3			3			2
11	6						2		1	4				2		4				1
12	1	2		1	3			1	2		1	1	1	3			1	1	1	1
13	1	3			1	1	3			1	2		1	2		1	2		1	1
14	3			4				1	2		1	5					1	3
15	1	2		5					1	2		2		1	1	2		2		1
16	1	1	2		1	4				3			3			3			2
17	2		1	1	1	1	1	3			1	1	1	3			1	1	1	1
18	5					1	3			3			3			3			2
19	1	2		2		4				1	2		2		1	1	2		2
Sheet5

Cell Formulas
Range	Formula
AV6:BO19	AV6	=IF(AND(ISNUMBER(B6),C6=B6),"",COUNTA(C6:INDEX(C6:$V6,,IFERROR(MATCH(--NOT(C6=1),C6:$V6,0)-1,COLUMNS(C6:$V6)))))

 

[KRice]

Then aligned with the helper cells, your original data set and the formula that draws from the helper cells:

MrExcel_20240301.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
4	z1	z2	z3	z4	z5	z6	z7	z8	z9	z10	z11	z12	z13	z14	z15	z16	z17	z18	z19	z20				Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Sum
5	z1	z2	z3	z4	z5	z6	z7	z8	z9	z10	z11	z12	z13	z14	z15	z16	z17	z18	z19	z20				0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	Sum
6	1	0	0	0	1	0	1	0	1	1	0	0	1	0	1	1	0	0	1	0					1	3	1	1	1	1	2	2	1	1	2	2	1	1						20
7	1	1	1	0	1	1	0	0	1	0	1	1	0	0	1	0	1	1	0	0					3	1	2	2	1	1	2	2	1	1	2	2								20
8	1	0	0	0	1	0	1	0	1	1	0	0	1	0	1	1	0	0	1	0					1	3	1	1	1	1	2	2	1	1	2	2	1	1						20
9	1	0	0	1	1	0	1	0	0	1	1	0	0	0	0	1	1	0	0	0					1	2	2	1	1	2	2	4	2	3										20
10	0	1	0	1	0	0	1	0	0	1	1	1	0	0	0	1	1	1	0	0				1	1	1	1	2	1	2	3	3	3	2										20
11	1	1	1	1	1	1	0	0	1	0	0	0	0	1	1	0	0	0	0	1					6	2	1	4	2	4	1													20
12	1	0	0	1	0	0	0	1	0	0	1	0	1	0	0	0	1	0	1	0					1	2	1	3	1	2	1	1	1	3	1	1	1	1						20
13	0	1	1	1	0	1	0	0	0	1	0	0	1	0	0	1	0	0	1	0				1	3	1	1	3	1	2	1	2	1	2	1	1								20
14	0	0	0	1	1	1	1	0	1	1	0	1	1	1	1	1	0	1	1	1				3	4	1	2	1	5	1	3													20
15	0	1	1	0	0	0	0	0	1	0	0	1	1	0	1	0	0	1	1	0				1	2	5	1	2	2	1	1	2	2	1										20
16	1	0	1	1	0	1	1	1	1	0	0	0	1	1	1	0	0	0	1	1					1	1	2	1	4	3	3	3	2											20
17	0	0	1	0	1	0	1	0	0	0	1	0	1	0	0	0	1	0	1	0				2	1	1	1	1	1	3	1	1	1	3	1	1	1	1						20
18	0	0	0	0	0	1	0	0	0	1	1	1	0	0	0	1	1	1	0	0				5	1	3	3	3	3	2														20
19	1	0	0	1	1	0	0	0	0	1	0	0	1	1	0	1	0	0	1	1					1	2	2	4	1	2	2	1	1	2	2									20
Sheet5

Cell Formulas
Range	Formula
Z6:AS19	Z6	=IF(AND(COLUMNS($Z:Z)=1,$C6=1),"",IFERROR(INDEX($AV6:$BO6,,AGGREGATE(15,6,COLUMN($A:$T)/($AV6:$BO6<>""),COLUMNS($Z:Z)-($C6=1))),""))
AT6:AT19	AT6	=SUM(Z6:AS6)

 

[Peter_SSs]

If column W does not contain 0s or 1s and column Y does not contain numbers (both of which look like the case with your sample) and you want to try without helper cells ..

I can also only show part of the results due to XL2BB limitations.
Columns C:W exactly as you have shown in post 1.

24 03 02.xlsm
	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
4		Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Sum
5		0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	0	1	Sum
6			1	3	1	1	1	1	2	2	1	1	2	2	1	1						20
7			3	1	2	2	1	1	2	2	1	1	2	2								20
8			1	3	1	1	1	1	2	2	1	1	2	2	1	1						20
9			1	2	2	1	1	2	2	4	2	3										20
10		1	1	1	1	2	1	2	3	3	3	2										20
11			6	2	1	4	2	4	1													20
12			1	2	1	3	1	2	1	1	1	3	1	1	1	1						20
13		1	3	1	1	3	1	2	1	2	1	2	1	1								20
14		3	4	1	2	1	5	1	3													20
15		1	2	5	1	2	2	1	1	2	2	1										20
Count 0 1

Cell Formulas
Range	Formula
Z6:AS15	Z6	=IF(OR($C6*COLUMNS($Z:Z)=1,SUM($Y6:Y6)=COLUMNS($C6:$V6)),"",IFERROR(AGGREGATE(15,6,COLUMN($C6:$V6)/($D6:$W6<>$C6:$V6),COLUMNS($Z:Z)-$C6),COLUMN($V6))-IFERROR(AGGREGATE(15,6,COLUMN($C6:$V6)/($D6:$V6<>$C6:$U6),COLUMNS($Z:Z)-$C6-1),COLUMN($C6)-1))
AT6:AT15	AT6	=SUM(Z6:AS6)

 

[motilulla]

Then aligned with the helper cells, your original data set and the formula that draws from the helper cells:

KRice, I am amazed to see how you make formula congratulations, really it worked fine I can adapt the situation as per your layout also no problem. I am happy and appreciate your help.

I tried using different combination instead 0’s & 1’s…1’s & 2’s it looks, I need to change the formula to get correct results, I tried to make changes using many ways but has not been possible for me to get correct answer.

Please can you help to modify as per following scenario. Also may I ask you is it possible via VBA.

Consecutive Question.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
1
2
3
4			z1	z2	z3	z4	z5	z6	z7	z8	z9	z10	z11	z12	z13	z14	z15	z16	z17	z18	z19	z20				Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Sum
5			z1	z2	z3	z4	z5	z6	z7	z8	z9	z10	z11	z12	z13	z14	z15	z16	z17	z18	z19	z20				1	2	1	2	1	2	1	2	1	2	1	2	1	2	1	2	1	2	1	2	Sum
6			2	1	1	1	2	1	2	1	2	2	1	1	2	1	2	2	1	1	2	1					1	3	1	1	1	1	2	2	1	1	2	2	1	1						20
7			2	2	2	1	2	2	1	1	2	1	2	2	1	1	2	1	2	2	1	1					3	1	2	2	1	1	2	2	1	1	2	2								20
8			2	1	1	1	2	1	2	1	2	2	1	1	2	1	2	2	1	1	2	1					1	3	1	1	1	1	2	2	1	1	2	2	1	1						20
9			2	1	1	2	2	1	2	1	1	2	2	1	1	1	1	2	2	1	1	1					1	2	2	1	1	2	2	4	2	3										20
10			1	2	1	2	1	1	2	1	1	2	2	2	1	1	1	2	2	2	1	1				1	1	1	1	2	1	2	3	3	3	2										20
11			2	2	2	2	2	2	1	1	2	1	1	1	1	2	2	1	1	1	1	2					6	2	1	4	2	4	1													20
12			2	1	1	2	1	1	1	2	1	1	2	1	2	1	1	1	2	1	2	1					1	2	1	3	1	2	1	1	1	3	1	1	1	1						20
13			1	2	2	2	1	2	1	1	1	2	1	1	2	1	1	2	1	1	2	1				1	3	1	1	3	1	2	1	2	1	2	1	1								20
14			1	1	1	2	2	2	2	1	2	2	1	2	2	2	2	2	1	2	2	2				3	4	1	2	1	5	1	3													20
15			1	2	2	1	1	1	1	1	2	1	1	2	2	1	2	1	1	2	2	1				1	2	5	1	2	2	1	1	2	2	1										20
16			2	1	2	2	1	2	2	2	2	1	1	1	2	2	2	1	1	1	2	2					1	1	2	1	4	3	3	3	2											20
17			1	1	2	1	2	1	2	1	1	1	2	1	2	1	1	1	2	1	2	1				2	1	1	1	1	1	3	1	1	1	3	1	1	1	1						20
18			1	1	1	1	1	2	1	1	1	2	2	2	1	1	1	2	2	2	1	1				5	1	3	3	3	3	2														20
19			2	1	1	2	2	1	1	1	1	2	1	1	2	2	1	2	1	1	2	2					1	2	2	4	1	2	2	1	1	2	2									20
Sheet5

Cell Formulas
Range	Formula
AT6:AT19	AT6	=SUM(Z6:AS6)

Have a good time and good luck.

Kind regards,
Moti

 

[motilulla]

If column W does not contain 0s or 1s and column Y does not contain numbers (both of which look like the case with your sample) and you want to try without helper cells ..

I can also only show part of the results due to XL2BB limitations.
Columns C:W exactly as you have shown in post 1.

Peter_SSs, I am amazed and happy to get solution exactly as per my layout. I do appreciate your help.

I tried using different combination as per Post#5 instead 0’s & 1’s…1’s & 2’s it looks, I need to change the formula to get correct results, I tried to make changes using many ways but has not been possible for me to get correct answer.

Please can you help to modify as per following scenario. Also may I ask you are it possible via VBA?

Have a good time and good luck.

Kind regards,
Moti

 

[Peter_SSs]

I tried using different combination as per Post#5 instead 0’s & 1’s…1’s & 2’s it looks, I need to change the formula to get correct results,

Yes, the formula was written to answer the question that you asked.

I have table with 0’s and 1’s…I need a formula or VBA which can count consecutive 0’s and 1’s

Please can you help to modify as per following scenario.

I don't know. It depends just what the scenario is. Some more (small but representative) sample data & expected results with new explanation?

 

[motilulla]

Yes, the formula was written to answer the question that you asked.

Peter_SSs, yes it is true and worked fine.

I don't know. It depends just what the scenario is. Some more (small but representative) sample data & expected results with new explanation?

Peter_SSs, thank you, sure data 1’s & 2’s in columns C:V, and expected count result are shown in the columns X:AQ, & sum in the Column AR. Hope this help.

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1
2
3
4		S.N	z1	z2	z3	z4	z5	z6	z7	z8	z9	z10	z11	z12	z13	z14	z15	z16	z17	z18	z19	z20		Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Sum
5		S.N	z1	z2	z3	z4	z5	z6	z7	z8	z9	z10	z11	z12	z13	z14	z15	z16	z17	z18	z19	z20		1	2	1	2	1	2	1	2	1	2	1	2	1	2	1	2	1	2	1	2	Sum
6		1	2	1	1	1	2	1	2	1	2	2	1	1	2	1	2	2	1	1	2	1			1	3	1	1	1	1	2	2	1	1	2	2	1	1						20
7		2	2	2	2	1	2	2	1	1	2	1	2	2	1	1	2	1	2	2	1	1			3	1	2	2	1	1	2	2	1	1	2	2								20
8		3	2	1	1	1	2	1	2	1	2	2	1	1	2	1	2	2	1	1	2	1			1	3	1	1	1	1	2	2	1	1	2	2	1	1						20
9		4	2	1	1	2	2	1	2	1	1	2	2	1	1	1	1	2	2	1	1	1			1	2	2	1	1	2	2	4	2	3										20
10		5	1	2	1	2	1	1	2	1	1	2	2	2	1	1	1	2	2	2	1	1		1	1	1	1	2	1	2	3	3	3	2										20
11		6	2	2	2	2	2	2	1	1	2	1	1	1	1	2	2	1	1	1	1	2			6	2	1	4	2	4	1													20
12		7	2	1	1	2	1	1	1	2	1	1	2	1	2	1	1	1	2	1	2	1			1	2	1	3	1	2	1	1	1	3	1	1	1	1						20
13		8	1	2	2	2	1	2	1	1	1	2	1	1	2	1	1	2	1	1	2	1		1	3	1	1	3	1	2	1	2	1	2	1	1								20
14		9	1	1	1	2	2	2	2	1	2	2	1	2	2	2	2	2	1	2	2	2		3	4	1	2	1	5	1	3													20
15		10	1	2	2	1	1	1	1	1	2	1	1	2	2	1	2	1	1	2	2	1		1	2	5	1	2	2	1	1	2	2	1										20
16		11	2	1	2	2	1	2	2	2	2	1	1	1	2	2	2	1	1	1	2	2			1	1	2	1	4	3	3	3	2											20
17		12	1	1	2	1	2	1	2	1	1	1	2	1	2	1	1	1	2	1	2	1		2	1	1	1	1	1	3	1	1	1	3	1	1	1	1						20
18		13	1	1	1	1	1	2	1	1	1	2	2	2	1	1	1	2	2	2	1	1		5	1	3	3	3	3	2														20
19		14	2	1	1	2	2	1	1	1	1	2	1	1	2	2	1	2	1	1	2	2			1	2	2	4	1	2	2	1	1	2	2									20
20		15	1	1	1	1	2	1	2	2	1	2	2	1	2	2	1	2	2	1	2	2
21		16	1	1	1	2	2	1	1	2	1	2	1	1	2	1	1	2	1	1	2	1
22		17	2	1	2	1	1	2	1	1	1	2	1	1	1	2	1	2	1	1	1	2
23		18	1	1	1	1	1	2	1	1	2	1	2	2	2	1	2	1	2	2	2	1
24		19	2	1	1	2	1	1	2	1	1	2	1	1	1	2	1	2	1	1	1	2
25		20	1	1	1	1	2	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
Sheet1

Cell Formulas
Range	Formula
AR6:AR19	AR6	=SUM(X6:AQ6)

Kind regards,
Moti

 

[Peter_SSs]

Put this formula in X6 and copy across and down.

=IF(OR(AND($C6<>$X$5,COLUMNS($X:X)=1),SUM($W6:W6)=COLUMNS($C6:$V6)),"",IFERROR(AGGREGATE(15,6,COLUMN($C6:$V6)/($D6:$W6<>$C6:$V6),COLUMNS($X:X)-($C6<>$X$5)),COLUMN($V6))-IFERROR(AGGREGATE(15,6,COLUMN($C6:$V6)/($D6:$V6<>$C6:$U6),COLUMNS($X:X)-($C6<>$X$5)-1),COLUMN($C6)-1))

I think that should work with any pair of numbers you use in the left section. For example

24 03 02.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
4	z1	z2	z3	z4	z5	z6	z7	z8	z9	z10	z11	z12	z13	z14	z15	z16	z17	z18	z19	z20		Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Count	Sum
5	z1	z2	z3	z4	z5	z6	z7	z8	z9	z10	z11	z12	z13	z14	z15	z16	z17	z18	z19	z20		4	7	4	7	4	7	4	7	4	7	4	7	4	7	4	7	4	7	4	7	Sum
6	7	4	4	4	7	4	7	4	7	7	4	4	7	4	7	7	4	4	7	4			1	3	1	1	1	1	2	2	1	1	2	2	1	1						20
7	7	7	7	4	7	7	4	4	7	4	7	7	4	4	7	4	7	7	4	4			3	1	2	2	1	1	2	2	1	1	2	2								20
8	7	4	4	4	7	4	7	4	7	7	4	4	7	4	7	7	4	4	7	4			1	3	1	1	1	1	2	2	1	1	2	2	1	1						20
9	7	4	4	7	7	4	7	4	4	7	7	4	4	4	4	7	7	4	4	4			1	2	2	1	1	2	2	4	2	3										20
10	4	7	4	7	4	4	7	4	4	7	7	7	4	4	4	7	7	7	4	4		1	1	1	1	2	1	2	3	3	3	2										20
Count Sequences

Cell Formulas
Range	Formula
X6:AQ10	X6	=IF(OR(AND($C6<>$X$5,COLUMNS($X:X)=1),SUM($W6:W6)=COLUMNS($C6:$V6)),"",IFERROR(AGGREGATE(15,6,COLUMN($C6:$V6)/($D6:$W6<>$C6:$V6),COLUMNS($X:X)-($C6<>$X$5)),COLUMN($V6))-IFERROR(AGGREGATE(15,6,COLUMN($C6:$V6)/($D6:$V6<>$C6:$U6),COLUMNS($X:X)-($C6<>$X$5)-1),COLUMN($C6)-1))
AR6:AR10	AR6	=SUM(X6:AQ6)

 

[motilulla]

Put this formula in X6 and copy across and down.

=IF(OR(AND($C6<>$X$5,COLUMNS($X:X)=1),SUM($W6:W6)=COLUMNS($C6:$V6)),"",IFERROR(AGGREGATE(15,6,COLUMN($C6:$V6)/($D6:$W6<>$C6:$V6),COLUMNS($X:X)-($C6<>$X$5)),COLUMN($V6))-IFERROR(AGGREGATE(15,6,COLUMN($C6:$V6)/($D6:$V6<>$C6:$U6),COLUMNS($X:X)-($C6<>$X$5)-1),COLUMN($C6)-1))

I think that should work with any pair of numbers you use in the left section. For example

Peter_SSs, this is the supreme solution. I tried changing variety of pair combinations truly this worked with all outstanding.

Thank you so much for optimum answer.

Good night, nice weekend and good luck.

Kind regards,
Moti