# How do I sum a column of cells by date?

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?

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 of `IF` and `AND`:

``````=ARRAYFORMULA(SUM(IF(MONTH(\$A\$1:\$A\$5)=MONTH(E1), IF(YEAR(\$A\$1:\$A\$5)=YEAR(E1), \$B\$1:\$B\$5))))
``````

The second uses the `FILTER` function. This method will return a `#N/A` error if `FILTER` doesn't find any matches for the conditions. `FILTER` takes each condition as a separate argument:

``````=SUM(FILTER(\$B\$1:\$B\$5, MONTH(\$A\$1:\$A\$5)=MONTH(E1), YEAR(\$A\$1:\$A\$5)=YEAR(E1)))
``````

The third uses `INDEX` and `SUMPRODUCT`:

``````=INDEX(SUMPRODUCT((MONTH(\$A\$2:\$A\$6)=MONTH(E2))*(YEAR(\$A\$2:\$A\$6)=YEAR(E2))*\$B\$2:\$B\$6), 1)
``````

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.

For a solution with query, you can try:

``````=ArrayFormula(query({text(A1:A, "mm/yy"), B1:B}, "select Col1, sum(Col2) where not(Col1) contains '99' group by Col1 "))
``````
• Can you explain this query a little? Specifically, I'm unsure what `contains '99'` is used for, since according to the documentation, `contains` is used for substring matches. Jan 11 '16 at 15:37
• The text function converts col A to a 'mm/yy' format. If there are empty cells in col A they will be converted to 12/99 by using where not(Col1) contains '99' we are excluding those rows.
– JPV
Jan 11 '16 at 18:52