Forum Discussion

tara627's avatar
tara627
Copper Contributor
Oct 20, 2022

Count consecutive cells that contain yes, reset count after no

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! 

excel
Formulas and Functions
office 365

1 Reply

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Oct 20, 2022

    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))))

     

Resources