Currently, I have a spreadsheet which has a number of customer sheets, each with a pivot table showing the month in the row I
and the total income for each month in row L
.
On a separate sheet I have a query that sums all of the month totals (This just shows 2 Customer sheets, normally these sheets are given the name of the customer):
=query({'Sheet1'!I2:I,'Sheet1'!L2:L;'Sheet2'!I2:I,'Sheet2'!L2:L},
"Select Col1, sum(Col2) group by Col1 label Col1 'Month'")
Currently this shows:
| Month | sum
|
| 2018/12 | £ 35.00
| 2019/01 | £ 155.00
| 2019/02 | £ 60.00
| 2019/03 | £ 210.00
| Grand Total | £ 460.00
I have been asked to add a column for each customer sheet which shows the value the query took before it did the SUM, I want each column to be named as the Sheet the column came from. So it should look like:
| Month | sum | Sheet1 | Sheet2
|
| 2018/12 | £ 35.00 | £10 | £25
| 2019/01 | £ 155.00 | £100 | £55
| 2019/02 | £ 60.00 | £30 | £30
| 2019/03 | £ 210.00 | £110 | £100
| Grand Total | £ 460.00
If the totals showed under each sheet name it wouldn't hurt but isn't necessary. The sum could be on the left or right, I'm not worried about it's positioning.
Is there any way to do this in Google Sheets? I have been playing around with query a bit, but haven't managed to get it to display more than 2 Columns.