I've got a series of withdrawals and deposits for an account, each in a row. I'd like another column that for each row, displays the total amount in the account after that row's withdrawal or deposit. I'd like this to be added automatically with each new row. I've tried a whole bunch of things with ArrayFormula and SUM, and haven't gotten it to work. Here's an example of what I'm going for:

Row |    A   |   B   |
 1  | Amount | Total |
 2  |   500  |  500  |
 3  |   20   |  520  |
 4  |  -50   |  470  |
 5  |   60   |  530  |
 6  |  -30   |  500  |

When I add a new row (#7) and only specify that it's a deposit of 10, I'd like the total column to show me 510.

Is there any way to do this?


The easiest way is to put this formula into B7: =B6+A7, then copy it downwards as you add more rows.

You could enclose it in an IF function and prefill column B so it only shows data when column A is non-blank.

  • Thanks for the response! I actually tried something similar, but if I'm copying I could just do the math myself and add it every time. I'm looking for an automated solution. – BenjaminRH May 12 '14 at 21:00
  • 1
    The suggestion is to prefill: do the copy and paste once, but into every cell in column B – MaryC.fromNZ May 13 '14 at 7:32
  • Thanks for the clarification, @MaryC.fromNZ. Not a bad approach. But there's a better, cleaner way to do this with Array Formula and that's what I'm looking for. – BenjaminRH May 13 '14 at 9:56
  • 2
    You might like to edit your original question to say that an array formula is what you are looking for. (Personally I don't like using a more-complex approach when a simpler one will do the job, so tend to avoid array formulae. YMMV) – MaryC.fromNZ May 13 '14 at 11:33
  • I agree with @MaryC.fromNZ. Simpler is better, especially when it comes to spreadsheets. – cpilko May 13 '14 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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