array formula to auto increment if conditions-CodePudding

Need array formula to auto increment column A when date is set and amount is not equal to 0

CodePudding user response：

Here's one way you could do that:

=ArrayFormula(IF(B2:B*(C2:C&""<>"0");SCAN(;ROW(B2:C)-1;LAMBDA(a;c;a IF(INDEX(B2:B;c)*INDEX(C2:C&""<>"0";c);1)));))

CodePudding user response：

Use scan(), like this:

=arrayformula( 
  if( 
    len(B2:B) * (to_text(C2:C) <> "0"); 
    scan( 
      ""; isdate_strict(B2:B) * (to_text(C2:C) <> "0"); 
      lambda( 
        result; increment; 
        result   increment
      )
    ); 
    iferror(1/0) 
  ) 
)