Count most recent consecutive occurrences of text value in a row

[pawneeranger]

I am keeping a progress tracker and want to know how many weeks in a row that something has been "off track" but only if it has been off track in the most recent week. For instance in the table below, I want to be able to ask Excel, "IF a project is off track in Week 4, how many weeks IN A ROW has it been off track?" (The answer being 2 - it has been off track for two consecutive weeks.)

Week 1	Week 2	Week 3	Week 4
Off Track	On Track	Off Track	Off Track

 

[Anthony47]

Please specify which Office version you do use

For Office 365 or Office 2021 try

=LET(wStat,B2:E2,Array,ROW(INDIRECT("1:"&COLUMNS(wStat))),MAX(IF(RIGHT(TEXTJOIN("",TRUE,IF(wStat="Off Track",1,0)),Array)=REPT("1",Array),Array,0)))

Instead of B2:E2 specify the range with the week status

For older version, try

=MAX(IF(RIGHT(TEXTJOIN("",TRUE,IF(B2:E2="Off Track",1,0)),ROW(INDIRECT("1:" &COLUMNS(B2:E2))))=REPT("1",ROW(1:4)),ROW(INDIRECT("1:"&COLUMNS(B2:E2))),0))

Again, replace those B2:E2 with the range of your week status

 

[Peter_SSs]

Does this do what you want?

22 07 28.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Result		Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8
2	2		Off Track	On Track	Off Track	Off Track
3	4		Off Track	Off Track	Off Track	Off Track
4	0		On Track	On Track	On Track	On Track
5	1		Off Track	On Track	On Track	Off Track
6	0		Off Track	Off Track	Off Track	On Track
Off Track

Cell Formulas
Range	Formula
A2:A6	A2	=COUNTA(C2:J2)-IFERROR(AGGREGATE(14,6,(COLUMN(C2:J2)-COLUMN(C2)+1)/(C2:J2="On Track"),1),0)

For older version, try

Would only be for one older version - TEXTJOIN only appeared in Excel 2019.

 

[Anthony47]

TextJoin is available in Office 2016, for sure is not available in Office 2010; don't know what about Office 2013

Your formula is quite smart, I only wonder why you didn't publish it 12 hours ago, I would have been saving 15 minutes of my time

 

[Fluff]

TextJoin is available in Office 2016

Fraid not, as Peter said, it 1st appeared in xl2019

 

[Peter_SSs]

TextJoin is available in Office 2016,

Hmm, I was just going on the Microsoft help re TEXTJOIN

Could be part of an Add-In for some people?

 

[pawneeranger]

Unfortunately I’m just using Sheets (work computer - max - don’t even get me started) but I will give all of these a shot!

 

[Peter_SSs]

Unfortunately I’m just using Sheets

In that case the question should not be in the Excel Questions forum.

Also then wondering why you wrote this?

I want to be able to ask Excel, ..

I have moved it to the appropriate forum.

 

[Anthony47]

I did not expect this hot debate about TextJoin...
And anyway two versions is much more that only one