Count consecutive values starting from last zero in row

[cholcfp]

I have a spreadsheet where I need to calculate the consecutive months in a row where a site is showing up 'flagged' as a 1. This needs to restart every time they show up as 0 (i.e, fall off the flagged list). The max frequency formulas I have tried from other forums don't work as they return the max consecutive values despite the site falling off in current month(s).

 

[Fluff]

Hi & welcome to MrExcel.
How about

=13-LOOKUP(2,1/(B2:M2=0),COLUMN(B2:M2))

 

[cholcfp]

Hi & welcome to MrExcel.
How about

=13-LOOKUP(2,1/(B2:M2=0),COLUMN(B2:M2))

Hi! Thanks for the quick reply! This worked for most rows - but did not return expected results on rows 7 & 8, screenshot attached.

 

[Fluff]

I forgot to account for a row with no zeros, so I have corrected that, but the two rows you have flagged should work

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		1	1	1	1	1	0	0	0	0	0	0	0		0
3		0	1	1	1	1	1	1	1	0	0	0	1		1
Master

Cell Formulas
Range	Formula
O2:O3	O2	=IFNA(13-LOOKUP(2,1/(B2:M2=0),COLUMN(B2:M2)),12)

Check that those 0 are real numbers & not text.

 

[cholcfp]

I forgot to account for a row with no zeros, so I have corrected that, but the two rows you have flagged should work

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2		1	1	1	1	1	0	0	0	0	0	0	0		0
3		0	1	1	1	1	1	1	1	0	0	0	1		1
Master

Cell Formulas
Range	Formula
O2:O3	O2	=IFNA(13-LOOKUP(2,1/(B2:M2=0),COLUMN(B2:M2)),12)

Check that those 0 are real numbers & not text.

Thank you! That works on my slimmed down sample data, but can't figure out what I need to change in my larger data set that has 25 months and will be adding additional month as year progresses. I tried to simply adjust 13 to 26 and 12 to 25, but it's returning negative values so I am definitely not doing something correct.

 

[Fluff]

Ok, how about

=LET(Data,D2:AB2,c,COLUMNS(Data),IFNA(LOOKUP(2,1/(Data=0),SEQUENCE(,c,c-1,-1)),c))

 

[cholcfp]

Ok, how about

=LET(Data,D2:AB2,c,COLUMNS(Data),IFNA(LOOKUP(2,1/(Data=0),SEQUENCE(,c,c-1,-1)),c))

That worked! Thanks so much!

 

[Fluff]

You're welcome & thanks for the feedback.