I have a spreadsheet with these data (all of these dates and amounts are arbitrary):
A B
12/1/2015 700
1/7/2016 100
1/13/2016 200
2/5/2016 300
3/19/2016 500
and another column with the first date of each month. I'm trying to sum the data by month/year to get something like this:
E F
12/1/2015 700
1/1/2016 300
2/1/2016 300
3/1/2016 500
Entering the dates into the first column of the pseudo pivot table isn't the problem; it's summing the data by date. In column F, I've tried this:
=ARRAYFORMULA(SUM(IF(AND(MONTH($A$1:$A$5)=MONTH(E1), YEAR($A$1:$A$5)=YEAR(E1)), $B$1:$B$5)))
but this returns all zeros. How do I do this?
Best Answer
As I was building this question, I figured out several ways to achieve this, so I went ahead and shared the information.
There are several ways to do this. The first is a variation on your original syntax, but using nested
IF
statements instead ofIF
andAND
:The second uses the
FILTER
function. This method will return a#N/A
error ifFILTER
doesn't find any matches for the conditions.FILTER
takes each condition as a separate argument:The third uses
INDEX
andSUMPRODUCT
:In each of these examples, I assumed that the data were in columns A and B, the "pivot table" dates were in column E, and the aggregated data are placed in column F.
There might be a way to do this with the
QUERY
function that provides an interface to the Google Visualization API Query Language, but I'm not sure. I don't know if such a query would dynamically update, either.