Google Sheets – Group Values by Month and Sum Previous Values

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

I would like to sum the values of a table grouped by months but adding up the previous months.

Example:

Result that I want:

Result

The closest I've come so far is:

=Query(B2:C4;"select sum(C), B group by B"; -1)

Best Answer

=QUERY({B2:C}; "select Col1,sum(Col2) 
                where Col1 !='' 
                group by Col1 
                label sum(Col2)''"; 0)

=ARRAYFORMULA({"Month"\ "Total"; {
 QUERY(QUERY({B2:C}; "select toDate(Col1),sum(Col2) 
                      where toDate(Col1) is not null 
                      group by toDate(Col1) 
                      label sum(Col2)'',toDate(Col1)''"; 0); "select Col1"; 0)\ 
 MMULT(TRANSPOSE((ROW(INDIRECT("A1:A"&COUNTA(
 QUERY(QUERY({B2:C}; "select toDate(Col1),sum(Col2) 
                      where toDate(Col1) is not null 
                      group by toDate(Col1) 
                      label sum(Col2)'',toDate(Col1)''"; 0); "select Col2"; 0))))<=
 TRANSPOSE(ROW(INDIRECT("A1:A"&COUNTA(
 QUERY(QUERY({B2:C}; "select toDate(Col1),sum(Col2) 
                      where toDate(Col1) is not null 
                      group by toDate(Col1) 
                      label sum(Col2)'',toDate(Col1)''"; 0); "select Col2"; 0))))))*
 QUERY(QUERY({B2:C}; "select toDate(Col1),sum(Col2) 
                      where toDate(Col1) is not null 
                      group by toDate(Col1) 
                      label sum(Col2)'',toDate(Col1)''"; 0); "select Col2"; 0)); SIGN(
 QUERY(QUERY({B2:C}; "select toDate(Col1),sum(Col2) 
                      where toDate(Col1) is not null 
                      group by toDate(Col1) 
                      label sum(Col2)'',toDate(Col1)''"; 0); "select Col2"; 0)))}})

Related Solutions

Google-sheets – How to sum values by month

Please try putting the start and end dates (say in G1 and H1) and a formula such as:

=sumifs(C:C,B:B,">="&G1,B:B,"<="&H1)

Alternatively, since all your dates appear to be this year, you might extract the month number with something like:

=month(B1)

copied down to suit and then create a pivot table with that number for Rows.

Google Sheets – Sum Values for Each Month

The relevant commands are named filter and query. (There are also pivot table reports that could be used for summarizing data.) The details of implementation vary based on:

Whether your date strings like 2015-12-10 are formatted as strings or dates.
Whether you want to have 0 for months where no transactions are recorded (if so, then you have to provide the list of months for the summary table manually).

I'll give a version that operates with dates as dates (as they ought to be stored) and outputs data for all months present in the table (so, a month with no records will be absent).

The formula assumes the data is in columns A and B. Its explanation is below.

=query(arrayformula({year(A1:A)&"-"&right("0"&month(A1:A),2), B1:B}), "select Col1, sum(Col2) where Col2 is not null group by Col1 order by Col1 asc label Col1 'Month', sum(Col2) 'Total'")

Preparation

The formula arrayformula({year(A1:A)&"-"&right("0"&month(A1:A),2), B1:B}) prepares a table for querying. The second column is just your B column. The first column is the month (as text) in format 2016-01. The slightly cumbersome right("0"&month(A1:A),2) is there to get 01 rather than 1 for January.

Query

The query language is SQL-like. Here is the query string I used, with linebreaks:

select Col1, sum(Col2) 
where Col2 is not null 
group by Col1 
order by Col1 asc 
label Col1 'Month', sum(Col2) 'Total'

Here, Col1 and Col2 refer to the columns of the table prepared at the first step. The query selects months and the total for each month, sorting them in order. The labels are optional, inserted just to make the output look better.

Remark

If you don't want to have a long formula like that, the preparation step can be a separate formula, creating a helper table, which the query command can then refer to.