How to group data by month in Google Sheets

Question

I have a Google Sheet which contains various metrics for each day. Each day has it's own row:

day        | cost | impressions | clicks | conversions
2014-02-01 | $45  | 3,540       | 80     | 5
2014-02-02 | $49  | 3,700       | 88     | 7
2014-02-03 | $42  | 3,440       | 79     | 7
2014-02-04 | $51  | 3,940       | 91     | 10

I want to pivot the table and summarize the cost, impressions, clicks and conversion columns by month (not by day) like this:

Month        | Sum of cost | Sum of impressions | Sum of clicks | Sum of conversions
February     | $187        | 14620              | 338           | 29

In Excel I just used the Group by function, but I see it's not available in Google Sheets.

Answer 1

Using the Data->Pivot table report... offers exactly what you are asking for. It might be possible that you have to use the "New Google Sheets".
I use that as a default setting and it was easy to achieve what you wanted, similar as in Excel.

Here in my answer I explain how to enable the new spreadsheets.

---

It seems I did not completly understand the problem.

First we need to have the month. For that add a new column to extract the date using =MONTH(DATE_COLUMN).

Then create a Pivot report:

Answer 2

Google Sheets now supports this as 'Create pivot date group'

In the Pivot table, once you've added your date/time column as rows:

1. Right-click on one of the values in the Pivot table,
2. Choose 'Create pivot date group'
3. Choose the desired grouping (e.g., 'Month' or 'Year-Month')

Before + Visual Example:

After:

More Info

• Google's documentation on the feature: https://support.google.com/docs/answer/7572895

Answer 3

Without a helper column:

=query(A:E,"select sum(B), sum(C), sum(D), sum (E) group by month(A) offset 1")  

assuming day is in A1.

Documentation that does a much better job of describing how the above works than ever I could.

Answer 4

So for those who asked similar question as me, There is apparently no Group field function for Pivot in Google Sheet (as it there is in Excel) yet. Hence the simplest way is to add another column transforming your dates to the groups you need, e.g. weeks, months, years... Hopefully the functionality is soon in Google sheet too.

Answer 5

An easier way that I found was to convert the date cell to text in a new column.

=Text(A1, "mmm-yy")

You can then pivot and it will group by month.

Answer 6

I found a way I like better. I simply created a hidden column with the following steps:

---

• Round every day of the month to the first day of the month.

=A2 - day(A2) + 1 where A2 is the standard date

• Set the formatting to custom and choose month / year. This will require you to delete the day setting. So, it will display month / year when the actual value will be first day of month, month, year.
• The pivot table will now group all similar items together and display formatting from the column with month / year.

This way you'll get unique values as the years pass by. Myself I decided to hide the column in question because I have a Doc connected to this Sheet that pulls the first two rows as I add them.

Answer 7

None of the previous answers worked for me, because I use a form as input. You can't add formulas on the form-sheet.

Here is my work-around.

Reading the above, the Pivot table needs a column with month to select on. You can use a different criteria, like week or fridays. Or John, Doe, Foo or bar. Whatever you want to 'group'on.

In my case: date is in B, =month(B2) gives the month. So I needed an extra column with =month(B2).

Form-sheet doesn't allow formulas. And on a sheet with a form, you can't add a column with formulas (exactly: you can, but a new form-entry deletes the formula on the corresponding line).

Second sheets

A simple second sheet with =page1!A1 etc and adding the column month has the same problem. A new entry in the form => adds a row.

Solution

So I did the following.

• Added an extra sheet
• Used =importrange("longkeynumber";"sheet form!A1:E1000") on A1
• (importrange is to import a different document, but hey … it works)
• In F, used =if(B3>0;(MONTH(B3));"")
• When adding extra data in sheet A, formulas in sheet B with the importrange stays.
• Now you can add a pivot table, with (in this case) F as a row or column.

Answer 8

If you're already adding a new column, you can also just use eomonth("reference cell",0). That'll always grab the last day of the month. Or eomonth("reference cell",-1)+1 to get the beginning of the month.

The beauty with this approach instead of converting to text is that when you pivot the data. It'll automatically sort ascending left to right.