Counting consecutive values that appear a set number of times. CountIf?

[tminogue]

Hi Guys,

Hope you can help! If you guys could help me with this I would be hugely grateful as this would save me sooo much time every month.

Basically, I want to count the number of times 1 appears in 3 or more consecutive cells. Each cell only contains a 1 or 0.

Name	Apr 1st	Apr 2nd	Apr 3rd	Apr 4th	Apr 5th	Apr 6th	Apr 7th	Apr 8th	Apr 9th	Apr 10th	Apr 11th	Apr 12th	total
Joe Bloggs			1	1	1			1	1	1	1		2
Jim Bloggs				1	1		1	1	1				1

<tbody>
</tbody>

In the example above Joe blogs would return a value of 2 and Jim would return a value of 1


Any Help would be greatly appreciated!

T

 

[wideboydixon]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Apr 1st	Apr 2nd	Apr 3rd	Apr 4th	Apr 5th	Apr 6th	Apr 7th	Apr 8th	Apr 9th	Apr 10th	Apr 11th	Apr 12th	total
2	Joe Bloggs			1	1	1			1	1	1	1		2
3	Jim Bloggs				1	1		1	1	1				1
Sheet1

Cell Formulas
Range	Formula
N2	{=SUMPRODUCT(--(FREQUENCY(IF($B2:$M2=1,COLUMN($B2:$M2)),IF($B2:$M2<>1,COLUMN($B2:$M2)))>=3))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[tminogue]

WBD - Thank you very much this worked fantastically!