Search code examples
if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulas

ArrayFormula circular dependency detected

I need to make a helper column but am stuck because if I'm doing it for every row it's working, but if I make it an array formula it errors.

For a single cell, the formula is like this:

=if(AND(len(G2)>0, len(F2)>0),
    if(G2=G1, I1+1, "1"),
    if(AND(len(G2)=0, len(F2)=0), "", I1+1)
)

and I make an array formula like so:

={"Bundle Helper"; arrayformula(if(AND(len(G3:G)>0, len(F3:F)>0),
    if(G3:G=G2:G, I2:I+1,"1"),
    if(AND(len(G3:G)=0, len(F3:F)=0), "", I2:I+1))
)}

As I understand it, the problem is because I need a row before to add value in column "i" I1+1, but because it's an array so it becomes a problem I2:I+1.

Is there any other way or solution to this problem?

Link to Sheet

Solution 
  • =ARRAYFORMULA(IF(LEN(F2:F), COUNTIFS(F2:F, F2:F, ROW(F2:F), "<="&ROW(F2:F)), ))

    0