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.

Answer 2

You can do it with an array formula, i put the formula in A2 and in A1, i put the month number:

=ARRAYFORMULA(SUMPRODUCT(1*(MONTH(Sheet1!$A$2:$A$6)=A1)*Sheet1!$B$2:$B$6))

See the doc in action in Google Spreadsheet

[EDIT] If you want both year and month:

=ARRAYFORMULA(SUMPRODUCT(1*(YEAR(Sheet1!$A$2:$A$8)=A2)*(MONTH(Sheet1!$A$2:$A$8)=B2)*Sheet1!$B$2:$B$8))

Answer 3

in addition to contribution from JMax, let me present the following formula using the QUERY function ...

=query(index('Sheet3'!A:B); "select year(Col1)+(month(Col1)+1)/100,sum(Col2) where Col1 is not null group by year(Col1)+(month(Col1)+1)/100 label year(Col1)+(month(Col1)+1)/100 'mydate',sum(Col2) 'amount' ")

Notes: I have assumed the original data is in Sheet3 and my result presents the year and month combinationas yyyy.mm instead of yyyy-mm I have created an illustration:

Answer 4

The answer of matt burns and Cœur is pretty accurate and I would like to add a comment (I did not add it on the comment section as it is quite long)

You may want to use this formula instead: DATE(YEAR(A2);MONTH(A2);1) to group by year & month.

This way you will keep values as dates and therefore be able to apply any kind of date formatting or sorting in your pivot table.

---

Explanation you can skip:

Instead of TEXT(A2, "YYYY-MM") converts value in text and therefore remove freedom about further date manipulation.

DATE(YEAR(A2);MONTH(A2);1) remove the day and time from the date so for example 2018/05/03 and 2018/05/06 both become 2018/05/01 which is still a date which can then be grouped together.

By applying a format "YYYY-MM" directly on the pivot table you reach the same result.

Answer 5

You need to use an array approach. How to combine arrays you can learn here https://stackoverflow.com/a/42488671/1393023

Foremost QUERY

It's simple and transparent

=QUERY(
  ARRAYFORMULA({TEXT(Movements!A:A,"YYYY-MM"),Movements!B:B}),
  "select Col1,sum(Col2) group by Col1"
)

Secondly ARRAYFORMULA(SUMIF)

Just combine the functions to a chain

=SORT(UNIQUE(FILTER(
  {
    TEXT(Movements!A2:A,"YYYY-MM"),
    SUMIF(EOMONTH(Movements!A2:A,1),EOMONTH(Movements!A2:A,1),Movements!B2:B)
  },
  LEN(Movements!B2:B)
)),1,1)

My example

Answer 6

I was working with a similar issue. I found this formula to work well.

=SUMIF(ARRAYFORMULA(MONTH('Sheet1'!A:A)), A2, 'Sheet1'!B:B)

In cell A2 I put the month I wanted totaled.