Count two consecutive days off

[hoyos]

I have the following formula which I got from the forum and modified it slightly
=IF(COUNTIF(AY6:AY19,"1")>=2,"YES","NO")
Unfortunately it hasn't worked!!
What I am trying to achieve is the following: this formula is copied down column "AX". Down column "AY" has input whether a day off was taken that day by populating the cell with "1". and "0" if the individual worked. Now the formula should look at a block of 14 days previous to see if there was two consecutive days off and should indicate it with a "YES" if not then a "NO". It should only look for blocks of two days not single days. i.e if there was a block of four days then it should show with a "2" ( 2 sets of 2 consecutive days off). Appreciate any help please.

 

[AlanY]

try this, note that the arrays are offset by 1 row

Book1
	A	B
1	1	YES
2
3	1
4	1
5
6
7	1
8
9	1
10
11
12
13
14
Sheet1

Cell Formulas
Range	Formula
B1	B1	=IF(SUMPRODUCT(A1:A14,A2:A15)>0,"YES","NO")

 

[hoyos]

Many thanks, I shall try it out over the week to see if there are any other issues and I shall get back to you.

 

[hoyos]

Thank you very much that has done the trick, works well.

 

[AlanY]

Thank you very much that has done the trick, works well.

glad that work for you, thanks for the feedback