Wednesday, October 9, 2013

yogi_Make Budget Computations By Month Based On Data In Form Responses Sheet

                                          Google Spreadsheet   Post  #1392
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI

           updated Oct-26-2016 To Work With Google New Sheets   Oct 09, 2013
question by: Jandmd (!category-topic/docs/spreadsheets/HdBcYsqDPYw)
Sumifs? Arrayformula? Filter(Sum) with no N/a?
I am making a budget form for my wife and I to use. I will have the form as a link on our mobiles, then when we spend anything we can click on the month, then the category, and input an amount. That will transfer to the spreadsheet.

Then, I want that spreadsheet to have sheets for each month, so we can track when we are over/under budget in our spending.

So, the 1st new sheet will be called October.

I want the spent column in the new sheet to equal anything from the 1st sheet that has Oct in the month column, and the budget category in the Budget category column. Let's call it childcare.  But I am putting Childcare in the A column of the October sheet, so I can reference the column.  I have tried a few things, but not having luck.

My latest attempt is this guy
=sum(filter('Form Responses'!D:D;'Form Responses'!B:B = "Oct";'Form Responses'!C:C =A2))

and it works like a charm when there is data in the columns that correspond to the category, but when there is no data in the category, then it returns an #N/A instead of 0 or being blank, so it messes up the autosums at the bottom of the page where I am trying to total our budget, our spending, and our remaining each month.

Any help would be greatly appreciated.

I made a demo form here
with the accompanying spreadsheet here

There are categories that we don't spend on each month on our longer budget spreadsheet, so we really need it to work without the #N/A

Again, any help would be greatly appreciated