Count of Consecutive Letters in a Row

[Classy]

I am trying to use a formula to count the consecutive letters in a row from an attendance sheet. The letter is "Y" if they have attended and sometimes it is consecutive 2 or 3 times and then further in the table is consecutive 4 or 5 times. I would like the formula to display the higher of the consecutives.

Thanks

 

[Fluff]

Hi & welcome to MrExcel.
How about like

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

 

[hagia_sofia]

Another option:

=BYROW(A2:Z3,LAMBDA(x,MAX(SCAN(0,x,LAMBDA(a,v,IF(v="Y",a+1,0))))))

 

[Peter_SSs]

.. and another

=MAX(LEN(TEXTSPLIT(CONCAT(--(B2:Z2="Y")),0)))