Consecutive Values

[Fyldeboy]

I have 7 columns representing days of the week, they each contain a 0 or 1 to indicate if the person is working that day.
I need to create an array formula to measure how many days worked (1) until they have a day off - so number of consecutive 1s until 1st 0.
I also need to calculate how many 1s from the last 0 to the end of the row.

​

Staff​	Mon​	Tue​	Wed​	Thu​	Fri​	Sat​	Sun​	start​	end​
GD​	1​	1​	1​	0​	0​	1​	1​	3​	2​
DR​	0​	1​	1​	0​	1​	1​	1​	0​	3​
MD​	0​	1​	1​	1​	1​	1​	0​	0​	0​
DLH​	1​	0​	1​	1​	1​	0​	1​	1​	1​

 

[Toadstool]

Hi Fyldeboy,

Does this do what you ask?

Fyldeboy.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Staff	Mon	Tue	Wed	Thu	Fri	Sat	Sun	start	end
2	GD	1	1	1	0	0	1	1	3	2
3	DR	0	1	1	0	1	1	1	0	3
4	MD	0	1	1	1	1	1	0	0	0
5	DLH	1	0	1	1	1	0	1	1	1
Sheet2

Cell Formulas
Range	Formula
I2:I5	I2	=IF(B2=0,0,MATCH(0,C2:H2,0))
J2:J5	J2	=7-MATCH(2,1/(B2:H2=0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fyldeboy]

Thanks so much. Weirdly, that seemed to work on the 1st row but not afterwards. I certainly can't understand why rows 17/18 don't return 0, even I can see its a simple if statement!

 

[Fyldeboy]

slight ammendment to the image above - with formula done as Ctrl, Shift, Enter

 

[Toadstool]

Check that you're using the numeric 0 and not alphabetic o because they appear different on the image:

 

[Peter_SSs]

@Toadstool
Subject to my question below, your I2 formula could just be ..

=MATCH(0,B2:H2,0)-1

@Fyldeboy
Is it possible that somebody could work all 7 days?

If so, the amendment to my formula above would be (adjusted for your actual range of course)

=IFNA(MATCH(0,B2:H2,0)-1,7)

.. and Toadstool's other formula might become

=7-IFNA(MATCH(2,1/(B2:H2=0)),0)

 

[Fyldeboy]

You guys are amazing, it was some 0/O error, though the issue of them looking differently was just do do with them being formatted differently.

 

[Peter_SSs]

Glad you got it sorted - thanks to Toadstool's keen eyesight.