33

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.

18

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).

enter image description here

Then create a Pivot report:

enter image description here

  • tried the 'new google sheets' and there's no option to group dates by month in the pivot table reports, maybe you could shed some light? – Bogdan Mar 10 '14 at 22:06
  • Sorry, I thought the main problem was that you couldn't find grouping at all. Then why not adding a column that provides the month only and group by it? – StampedeXV Mar 11 '14 at 7:24
  • Unfortunately I can't add in another column, or change the formatting of the source sheet as it's being used to power a dashboard in Cyfe, but thanks for your answers... – Bogdan Mar 11 '14 at 22:23
  • You can add the column, then hide it so it is invisible. Still you can use it in the Pivot report. – StampedeXV Mar 12 '14 at 7:15
  • Excellent idea. I tried it, however Cyfe doesn't seem to be able to ignore the hidden columns. I'm just going to have to maintain 2 duplicate sheets with the source data to work around this.... – Bogdan Mar 12 '14 at 11:46
24

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

Once you've added your date/time column as a row:

  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:

Pivot table before grouping

After:

enter image description here

More Info

  • this should be the right answer probably – senseiwu Oct 5 '18 at 7:23
  • This is the best answer by far as of 2018 – Calvin Oct 17 '18 at 23:31
  • Can I use my own formulae with Pivot tables somehow? I want to get quantiles – Hubert Grzeskowiak Jul 9 at 2:08
  • 3
    I don't see this option in my date column – Ian Hyzy Sep 3 at 15:49
5

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.

  • 1
    This sounds really useful. Could you explain how it works? I've never used the Query function. – Shawn V. Wilson May 10 '18 at 20:10
  • Is it possible to group by month AND year? – Hubert Grzeskowiak Jul 9 at 2:08
  • @HubertGrzeskowiak Yes, it is – Lee Taylor Jul 21 at 15:18
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.

2

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.

1

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.
  • In user165410's answer, he states: - In F, used =if(B3>0;(MONTH(B3));"") This is a good solution, but it can be made even easier: - in F3, =arrayformula(if(b3:b>0;(month(b3:b));"")) What this does, is automatically put the month for every non-empty cell in the range B3:B. Inserting or deleting a row, or moving a row will NOT screw up the formulas! – Stefan van Aalst Feb 10 '16 at 13:58
  • When a new responses is added to a spreadsheet, a new row is inserted. Array formulas (multiple values result) could be used instead or scalar formulas (single values results). By the other hand, when the source data comes from the same file you could use array formulas to return the same result as IMPORTRANGE and they updated immediately – Rubén Nov 3 '16 at 16:40
  • I love this solution! in F3, =arrayformula(if(b3:b>0;(month(b3:b));"")) Only problem is when the data is for more than one year and each month gets combined with the same month in the previous year. – Vic M Mar 29 at 21:29
1

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.

1

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.

Formula for date and formatting

Custom Date Format

Pivot Table Results

protected by Community Mar 29 at 22:03

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.