## Tuesday, October 30, 2012

### Google Spreadsheet Formula for cummulative sum

Question:

I have a google doc with a very simple form and I want it to calculate data live based on responses to the form.

DATE
AMOUNT
DESCRIPTION
TOTAL SPENT TO DATE
MONEY REMAINING

The form fills out the Date, amount, and description categories, but I would like the spreadsheet to calculate the Total Spent to Date based on a sum of the Amount column, and the Money Remaining Column should subtract the Total Spent to Date from a Given total.  Can you help me?

I know how to do this in excel, but I'm having a hard time with it recognizing the Form.  I tried using the Arrayforumula() and it does recognize the form, but I am unsure how to populate the formula correctly.

Thanks for any help you can give!

Solution:

Suppose you have following data in your spreadsheet:

Then put the following formula in Cell E1:
=ArrayFormula(IF(LEN(C:C);IF(ROW(C:C)=1;"TOTAL SPENT TO DATE";SUMIF(ROW(C:C);"<="&ROW(C:C);C:C));IFERROR(1/0)))

Then put the following formula in Cell F1:

=ArrayFormula(IF(LEN(C:C);IF(ROW(C:C)=1;"MONEY REMAINING";H4-E:E);IFERROR(1/0)))

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,
Kishan,