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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Apr 1st
[/TD]
[TD]Apr 2nd
[/TD]
[TD]Apr 3rd
[/TD]
[TD]Apr 4th
[/TD]
[TD]Apr 5th
[/TD]
[TD]Apr 6th
[/TD]
[TD]Apr 7th
[/TD]
[TD]Apr 8th
[/TD]
[TD]Apr 9th
[/TD]
[TD]Apr 10th
[/TD]
[TD]Apr 11th
[/TD]
[TD]Apr 12th
[/TD]
[TD]total
[/TD]
[/TR]
[TR]
[TD]Joe Bloggs
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]Jim Bloggs
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[/TR]
</tbody>[/TABLE]

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!