I'm trying to combine arrayformula with an increment value based on a condition.

Basically I have a column called purpose, it basically has 2 values - Restock and Order. Upon order, column Count Order will display 1, otherwise, 0. On the column over, without arrayformula I'd add the row above with the value on the left, example

Purpose | Count Order | Increment
Restock | 0           | 0
Order   | 1           | 1
Restock | 0           | 1
Order   | 1           | 2

I'd normally use the code for C2=B2, then the subsequent line would be C3=B3+C2

With arrayformula, my formula that I came up with was

={"Increment"; arrayformula(if(A2:A="","", (B2:B)+(C1:C)))}

Which gives me a circular error. I'm guessing in part I'm trying to add the header which the only thing I can think of is have this array start on.

Is there a better way than to start lower with this formula?

enter image description here

Apologies, looks like there's a bit of discrepancy after I simplified my question.

I put the formula provided into my spreadsheet for cell E1, header fills, first row is empty because of the pivot (which if I remove, after I add new data, it doesnt check off the new data and the (blank).

I'm thinking instead of pivot do I need to write a query utilizing distinct for the timestamp and purpose columns, would that resolve my issue with the blank and the increment?


1 Answer 1


You are referencing column C which is the same column you put the formula, that would mean you are trying to add the number to itself infinitely...

={"Increment"; arrayformula(if(B2:B="","", sumif(row(B2:B),"<="&row(B2:B),B2:B)))}

Here we use row to tell it to only add the preceding rows.

  • Fiddled around with the code and simplified it by using unique and filter to pull in the timestamps related to order only, then used an incremental code to assign it an order instead. This would've worked great if I managed to solve it the other way. Thanks CodeCamper!
    – Felix Pau
    Commented Dec 15, 2019 at 1:37
  • @FelixPau You should delete your answer that is not an answer but a question, here is one way to solve your issue of data being on row 3 instead of row 2 ={"Increment";""; arrayformula(if(B3:B="","", sumif(row(B3:B),"<="&row(B3:B),B3:B)))}
    – CodeCamper
    Commented Dec 15, 2019 at 1:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.