Counting for 3 consecutive non zero numbers

[sonyaltec]

Need help for a formula to count how many consecutive non-zero months in a year but count resets after each 3 consecutive months is achieved.
eg1: if all months value >1, count as 4 consecutive 3-months block.
eg2: 1,1,0,1,1,1,1,0,0,1,1,1 = count as 2 consecutive 3-months block.
eg3: 1,1,1,1,1,0,1,1,1,1,1,1 = count as 3 consecutive 3-months block.

 

[bebo021999]

Is the "1,1,0,1,1,1,1,0,0,1,1,1" text string within a single cell (for ex: A1), or, spread into 12 single cells (for ex: A1:L1)?

 

[Legacy 143009]

Is the "1,1,0,1,1,1,1,0,0,1,1,1" text string within a single cell (for ex: A1), or, spread into 12 single cells (for ex: A1:L1)?

I assume the OP suggests the second option.

 

[JvdV]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1	1	0	1	1	1	1	0	0	1	1	1		2
2	1	1	1	1	1	0	1	1	1	1	1	1		3
Sheet7

Cell Formulas
Range	Formula
N1:N2	N1	=SUM(FLOOR(FREQUENCY(IF(A1:L1,COLUMN(A1:L1)),IF(A1:L1,0,COLUMN(A1:L1)))/3,1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Phuoc]

Try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1	2	3	4	5	6	7	8	9	10	11	12
2	1	1	1	1	0	1	1	1	0	1	1	1	2
3	1	1	1	1	1	1	1	1	1	1	1	1	4
Sheet1

Cell Formulas
Range	Formula
M2:M3	M2	=SUMPRODUCT(--(COUNTIF(OFFSET($A2,0,{0,3,6,9},1,3),1)=3))

.

 

[Legacy 143009]

Try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1	2	3	4	5	6	7	8	9	10	11	12
2	1	1	1	1	0	1	1	1	0	1	1	1	2
3	1	1	1	1	1	1	1	1	1	1	1	1	4
Sheet1

Cell Formulas
Range	Formula
M2:M3	M2	=SUMPRODUCT(--(COUNTIF(OFFSET($A2,0,{0,3,6,9},1,3),1)=3))

.

I think M2 should count 3 since A2:D2 contains a 3-month block.

 

[JvdV]

Try this

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1	2	3	4	5	6	7	8	9	10	11	12
2	1	1	1	1	0	1	1	1	0	1	1	1	2
3	1	1	1	1	1	1	1	1	1	1	1	1	4
Sheet1

Cell Formulas
Range	Formula
M2:M3	M2	=SUMPRODUCT(--(COUNTIF(OFFSET($A2,0,{0,3,6,9},1,3),1)=3))

.

Change A2 into a zero and you'll see this does not work unfortunately

 

[Phuoc]

...

 

[sonyaltec]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	1	1	0	1	1	1	1	0	0	1	1	1		2
2	1	1	1	1	1	0	1	1	1	1	1	1		3
Sheet7

Cell Formulas
Range	Formula
N1:N2	N1	=SUM(FLOOR(FREQUENCY(IF(A1:L1,COLUMN(A1:L1)),IF(A1:L1,0,COLUMN(A1:L1)))/3,1))
Press CTRL+SHIFT+ENTER to enter array formulas.

Thanks!