I have a spreadsheet with 3 columns:

``````| ITEM | COST | SUBTOTAL |
``````

Subtotal, for example, is `1000` to start, like:

``````--------------------------
| ITEM | COST | SUBTOTAL |
--------------------------
|      |      | 1000     |
``````

Then I want to be able to add an item like:

``````--------------------------
| ITEM | COST | SUBTOTAL |
--------------------------
|      |      | 1000     |
| item | 50   |          |
``````

I want subtotal to update now.

I know I can do:

``````MINUS(B3,C2)
``````

But I want it to be dynamic like:

``````MINUS(BX,CX)
``````

Where `BX` is `this` B and `CX` is `this` C -1 row (the row above the current one to subtract one)

How can I do this with Google Sheets?

Simply copy your formula from that cell to each cell below. Copying formulas with relative references (those without `\$`) adjusts them automatically.

I think you have your coordinates backwards, though. Cell C2 contains "1000" and cell B3 contains "50", so your formula in C3 should be `=MINUS(C2,B3)` (or `=C2-B3`).

• Oops, yes, it is backwards! How do I make "relative" refs and not absolute ones?
– Oscar Godson
Feb 1, 2011 at 19:54
• Nevermind! Yes this is easy, just copy and paste and it knows!
– Oscar Godson
Feb 1, 2011 at 19:57

``````=QUERY(ARRAYFORMULA(IF(B2:B,
MMULT(TRANSPOSE((ROW(B2:B)<=TRANSPOSE(ROW(B2:B)))*{C2; B3:B*-1}),
SIGN({C2; B3:B*-1})^2), IFERROR(1/0))), "where Col1 is not null", 0)``````

• running total minus Apr 11, 2019 at 1:57