0

I have a Google spreadsheet for expenses: one column for the YYYY-MM-DD date of the expense, one column for the category, and one column for the amount. This sheet has thousands of rows. The goal is a pivot table with MM-YYYY in the column, summing amounts by the month/year.

Usually, that's easy. I create the pivot table with the ungrouped variable in the column, and then right click and create a pivot table group. However, when I create a pivot table with the date as the column of the table, Google Sheets says

This pivot table is trying to produce too many columns. Make it smaller by adjusting the column breakouts or filtering the source data.

There isn't a column to right click on to create the group because Google Sheets couldn't produce any of the columns (see screenshot)

How do I create a pivot table group here? Is the only to create another column in the sheet with the raw data (I'd prefer not to do this because that sheet is an output from another program) that does the grouping with a formula?

screenshot showing that there are no columns to right click on in the pivot table

1 Answer 1

1

There are two options:

1 - Create a query, then create a Pivot table based on the query
2 - Create a query that includes a pivot table

Query then Pivot

Query - Use this formula:

=query({arrayformula(text(TO_DATE(DATEVALUE(A3:A81)),"YYYY-mm")),B3:C81},"select Col1, Col2, sum(Col3) group by Col1, Col2 label Col1 'Date',Col2 'Category',Sum(Col3) 'Total'")

Query


Pivot Table Pivot Table


Query with Pivot

Use this formula:
={query({arrayformula(text(TO_DATE(DATEVALUE(A2:A80)),"YYYY-mm")),B2:C80},"select Col1, sum(Col3) group by Col1 pivot Col2 label Col1 'Date',Sum(Col3) 'Total'"),QUERY({arrayformula(text(TO_DATE(DATEVALUE(A2:A80)),"YYYY-mm")),B2:C80}, "select sum(Col3) group by Col1 label sum(Col3) 'Month Total'");TRANSPOSE(QUERY({{arrayformula(text(TO_DATE(DATEVALUE(A2:A80)),"YYYY-mm")),B2:C80}}, "select sum(Col3) group by Col2 label sum(Col3) 'Total'")),query({QUERY({arrayformula(text(TO_DATE(DATEVALUE(A2:A80)),"YYYY-mm")),B2:C80}, "select sum(Col3) group by Col1 label sum(Col3) 'Month Total'")},"select sum(Col1) label sum(Col1) ''")}


Screenshot

query with pivot


Sample data

sample data

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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