UNTESTED. Assuming the year is always 2015 something like the following in K2 copied down to K13 might suit:
=IF(J2="December",SUMIF(A:A,"<"&DATEVALUE("12/1/2015"),E:E),SUMIF(A:A,"<"&DATEVALUE(ROW()-1&"/1/2015"),E:E))
I'm guessing you basically want to sum up the expenses for each month, and then find an average amount per month.
Let's first sum up expenses for each month. Each row in your spreadsheet has a date, but that does not immediately link it to other rows within the same month.
So let's introduce a new column Month, derived from the date. There is a formula =MONTH, so let's use that. It takes a date as parameter, and returns 1 for January, 2 for February and so on:
A: Date B: Amnt C: Month
11/16/2016 $133 =MONTH(A2): 11
1/7/2017 $ 30 =MONTH(A3): 1
1/15/2017 $ 50 =MONTH(A4): 1
2/21/2017 $120 =MONTH(A5): 2
2/28/2017 $300 =MONTH(A6): 2
Then we can write a =QUERY to group expenses per month:
=QUERY(A2:C;"select C, sum(B) group by C"; 0)
This should give us:
11 $133
1 $80
2 $420
So far, so good. But wait a minute: What if we add an expense ($44) for November 2017? That reveals a problem - November 2016 and November 2017 will be summed up together, so:
11 $177 ($133+$44)
1 $80
2 $420
So we don't want to group just by month - we want to group it by year/month, so that November 2016 is summed separately from November 2017.
We can fix that by appending =YEAR to the Month column:
A: Date B: Amnt C: Year/Month
11/16/2016 $133 =YEAR(A2) & "/" & MONTH(A2): 2016/11
1/7/2017 $ 30 =YEAR(A3) & "/" & MONTH(A3): 2017/1
1/15/2017 $ 50 =YEAR(A4) & "/" & MONTH(A4): 2017/1
2/21/2017 $120 =YEAR(A5) & "/" & MONTH(A5): 2017/2
2/28/2017 $300 =YEAR(A6) & "/" & MONTH(A6): 2017/2
11/1/2017 $ 44 =YEAR(A7) & "/" & MONTH(A7): 2017/11
That will automatically fix our query result, so it is now:
2016/11 $133
2017/1 $ 80
2017/2 $420
2017/11 $ 44
If our query result is output in column F, we can now get the =AVERAGE per year-month:
=AVERAGE(F2:F)
I have made an example spreadsheet to demonstrate this, feel free to copy it.
Best Answer
I've added a new sheet ("Erik Help") to your sample spreadsheet. It contains two formulas.
In A1:
=ArrayFormula(QUERY(FILTER({WEEKDAY(Sheet1!A2:A),Sheet1!E2:E},ISNUMBER(Sheet1!E2:E),Sheet1!E2:E>0),"SELECT Col1, COUNT(Col1), SUM(Col2) GROUP BY Col1 ORDER BY Col1 ASC LABEL Col1 'Day', COUNT(Col1) 'Count by Day', SUM(Col2) 'Sum by Day'"))In A12:
=ArrayFormula(QUERY(FILTER({VALUE(TEXT(Sheet1!A2:A,"mmm yyyy")),Sheet1!E2:E},ISNUMBER(Sheet1!E2:E),Sheet1!E2:E>0),"SELECT Col1, COUNT(Col1), SUM(Col2) GROUP BY Col1 ORDER BY Col1 ASC LABEL COUNT(Col1) '', SUM(Col2) ''"))Each references only
Sheet1.The main thing to notice in each of these formulas is that I performed a
FILTERbefore applying theQUERY. While theFILTERmakes it necessary toLABELcolumns at the end of theQUERY, it is actually still much more efficient, as you can see.Also note that I applied a CF rule to the range A12:C, to highlight every other non-blank row.