1

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?

1

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.

1

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. – Michael A 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.