Friday, January 8, 2016

yogi_Compute Month By Month Amount Spent And Running Balance Remaining From Monthly Budget Amount

Google Spreadsheet   Post  #2011
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Jan-08-2016
post by: David Leichter:!topic/docs/JEbGnQTIGjg;context-place=mydiscussions
Show Purchases Totaled for Each Month SUMIFS
I want to track spending, and make sure I stay under my organization's monthly budget.

As you can see in the example below, Column A contains the dates a purchase was made, Column C contains the total amount. Over in Column G, I have the months of the year and in column H is the total spend for each month.

In Column H I have a formula that works, but I wonder if there is a more elegant solution:

=SUMIFS(C$2:C, A$2:A, "<2/1/2016", A$2:A, ">12/31/2015")

This causes all of the purchases with a January 2016 Date to be summed next to January. I then cut and paste this formula down the column manually changing the date range in the formula each time.

I tried all the following, and either got an error or $0, as my answer:

=SUMIFS(C$2:C, A$2:A, "MONTH=1")
=SUMIFS(C$2:C, A$2:A, "<2/1/2016 AND >12/31/2015")