**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:

=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)))

=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,

I have a question, not sure where to post it....

ReplyDeleteI need to create a master sheet that has 14 other sperate sheets feed to it. Both the master and the separate sheets need to be live and update-able. Is this possible? If so, how do I do this? I have been playing with =importrange but am not able to add more then one other sheet. Help Please!

Hi! You may be able to help me.

ReplyDeleteI'd like to make onEdit(e) {} detect when a background cell has changed its colour...

actually, i'm not sure if onEdit is able to handdle this event (background Color change)

do you have a solution?