I would like to sum the values that match each month in a spreadsheet. The spreadsheet looks like this:
2015-12-10 15
2015-12-19 10
2016-01-02 15
2016-01-05 25
And I want to obtain this:
2015-12 25
2016-01 40
How can I do that?
I tried to look for a formula like =FOR or =SELECT but there is no such thing in Google Spreadsheets. Looks like I cannot select a range conditionally.
Best Answer
The relevant commands are named
filter
andquery
. (There are also pivot table reports that could be used for summarizing data.) The details of implementation vary based on:2015-12-10
are formatted as strings or dates.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.
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 cumbersomeright("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:
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.