Summarize grouping by year and month

Question

I have a worksheet Movements with two fields:

mydate      | amount
2009-01-01  | 10.00
2009-01-02  | 13.00
2009-02-01  | 11.00
2009-02-05  | 12.00
2009-02-08  | 52.00

I want to have in another worksheet MonthSum that displays the sums of the data in the column amount grouped by year+month of the column date:

mydate      | amount
2009-01     | 23.00
2009-02     | 75.00

I don't want to specify the cells where the spreadsheet has to sum, I want a generic formula to group my data per month+year. If I was on a MySQL database I would simply do:

SELECT DATE_FORMAT('%Y-%m', mydate), SUM(amount) 
FROM mytable GROUP BY DATE_FORMAT('%Y-%m', mydate)

I need the solution to work on Google Spreadsheets.

I think that possible solutions would be using SUMIF or pivot tables or Google Spreadsheets QUERY function.

Answer 1

This is what pivot tables are for.

I have created an example using your data.

First, add a column to format the date as your monthly string, eg "2009-01" with the formula:

TEXT(A2, "YYYY-MM")

---

Then highlight the data, and choose "data" > "pivot table report..."

---

For Rows, select "month"

---

For Values, select "amount"

---

TADA! That's it!

For a quick overview of pivot tables, see this Google blog post.