Consecutive formula, not counting most recent sequence

[jchick90]

This is my first post, so please go easy on me

I'm trying to calculate the number of consecutive cells in which a value appears, but the most right/recent set of consecutive only.

I'm currently using the following formula, but it's presenting the highest consecutive value, rather than the most recent.

=MAX(FREQUENCY(IF(B2:O2<>"",COLUMN(B2:O2)),IF(B2:O2="",COLUMN(B2:O2))))

The red values are the desired result, and those next to it are what the above formula is currently producing.




Any help is much appreciated!

Thanks so much in advanc

 

[Fluff]

How about
=LET(f,FREQUENCY(IF(B2:O2<>"",COLUMN(B2:O2)),IF(B2:O2="",COLUMN(B2:O2))),TAKE(FILTER(f,f<>0,0),-1))

 

[AliGW]

Please read the forum guidelines on cross-posting. In future, please disclose this yourself with a link or links. I am doing it for you this time:
https://www.excelforum.com/excel-formulas-and-functions/1419958-consecutive-count.html

A message to forum cross posters

Do you know why you're here? If you have been pointed to this page, it is not because someone is mad at you, but rather because we want to curb your posting habits before people start ignoring your posts. Online … Continue reading →

excelguru.ca

 

[p45cal]

or:

=LET(c,SCAN(0,B4:O4,LAMBDA(a,b,IF(b<>"",a+1,0))),TAKE(FILTER(c,c,0),,-1))

Note that this and @Fluff 's solution count a zero as a value, whereas the suggestion at your excelforum cross-post doesn't.

To answer your other question at ExcelForum, the header at the start of the run:

=LET(d,$B$3:$O$3,c,SCAN(,B4:O4,LAMBDA(a,b,IF(b<>"",a+1))),TAKE(FILTER(d,c=1,""),,-1))

or:

=TAKE(FILTER($B$3:$O$3,SCAN(,B4:O4,LAMBDA(a,b,IF(b<>"",a+1)))=1,""),,-1)

 

[caabyyc]

Option for your reference
p2=LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(TEXTJOIN(,,IF(B2:O2=""," ","/")))," ",REPT(" ",50)),50)))