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.

My headings are:

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:

http://igoogledrive.blogspot.com/p/paid-projects.html

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,

iGoogleDrive