Running Count reset at specific condition excel-CodePudding

I am trying to figure out a way to reset the cumulative counting every time I reach "SI".

For example i am doing a running total of word "NO" , if there are some blanks then result should be blank, if there is a "NO" after blank then it again start doing running count of "No", only it reset at word "SI".

I have write this formula

=IF(OR(E3="SI",E3=""),"",IF(E2="No",G2 1,IF(E3="No",COUNTA(E3))))

which working as here in the picture

CodePudding user response：

Could you just do this?

=IF(E3="No", IF(G2="", 1, G2 1), "")

This gave me the behaviour you were looking for in my test sheet.

CodePudding user response：

For a pull-down formula, try this in G3:

=IF(E3="NO",1,"")

and this in G4 pulled down:

=IF(E4<>"NO","",
  IF(MAX(IF(E$3:E3="SI",ROW(E$3:E3)))>MAX(IF(G$3:G3<>"",ROW(G$3:G3))),1,
  INDEX(G:G,MAX(IF(G$3:G3<>"",ROW(G$3:G3)))) 1)
)

If you have access to the Scan function in Excel 365, you can also do it with a single formula:

=LET(range,E3:E30,
values,SCAN(0,range,LAMBDA(a,c,IF(c="SI",0,IF(c="NO",a 1,a)))),
IF(range="NO",values,""))