Count consecutive cells that contain yes, reset count after no

[tara627]

Hi there! 

Does anyone know how to count the number of consecutive cells in a column that contain Yes, but reset the count if a cell contains No? 

I'm making a spreadsheet for a family health challenge, and we have to reach a consecutive 75 days, if we miss one, we start over. I'm hoping to find a way for the spreadsheet to track that. Thank you! 

[#2]

@tara627 

I see you have Excel 365.

Try this:

=LET(list,E1:E1000,blanks,COUNTBLANK(list),dynamic,DROP(list,-blanks),REDUCE("",dynamic,LAMBDA(a,v,IF(v="No",0,1+a))))