# SUM columns from other sheets and group by month, but also show the individual sheets values before doing SUM

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.

• I would rather not, is there some information I missed to help answer this question? Jan 19, 2019 at 18:43
• its always helpful to provide copy or dummy of the sheet to the person who is trying to answer your question especially when the answer is not a simple one (like in your case) eg. that person needs to invest (waste) time to recreate the whole scenario from scratch only from question itself, and only then when it has done the job, person can work on the solution to answer your question. (fyi, I already recreated it, so pls take that in account next time when you shall have another question) Jan 19, 2019 at 20:06

# one cell solution:

```={{QUERY({Sheet1!E3:F; Sheet2!E3:F; Sheet3!E3:F; Sheet4!E3:F; Sheet5!E3:F},
"select Col1, sum(Col2)
where Col1 is not null
group by Col1
label Col1 'Month', sum(Col2)'Sum'", 0)},
{"Sheet1"; ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE(SORT(
QUERY({Sheet1!E3:E; Sheet2!E3:E; Sheet3!E3:E; Sheet4!E3:E; Sheet5!E3:E},
"select Col1 where Col1 is not null"),1,1)), Sheet1!E3:F, 2, 0), ))},
{"Sheet2"; ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE(SORT(
QUERY({Sheet1!E3:E; Sheet2!E3:E; Sheet3!E3:E; Sheet4!E3:E; Sheet5!E3:E},
"select Col1 where Col1 is not null"),1,1)), Sheet2!E3:F, 2, 0), ))},
{"Sheet3"; ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE(SORT(
QUERY({Sheet1!E3:E; Sheet2!E3:E; Sheet3!E3:E; Sheet4!E3:E; Sheet5!E3:E},
"select Col1 where Col1 is not null"),1,1)), Sheet3!E3:F, 2, 0), ))},
{"Sheet4"; ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE(SORT(
QUERY({Sheet1!E3:E; Sheet2!E3:E; Sheet3!E3:E; Sheet4!E3:E; Sheet5!E3:E},
"select Col1 where Col1 is not null"),1,1)), Sheet4!E3:F, 2, 0), ))},
{"Sheet5"; ARRAYFORMULA(IFERROR(VLOOKUP(UNIQUE(SORT(
QUERY({Sheet1!E3:E; Sheet2!E3:E; Sheet3!E3:E; Sheet4!E3:E; Sheet5!E3:E},
"select Col1 where Col1 is not null"),1,1)), Sheet5!E3:F, 2, 0), ))}}``` # 2-cell solution:

```=QUERY({Sheet1!E3:F; Sheet2!E3:F; Sheet3!E3:F; Sheet4!E3:F; Sheet5!E3:F},
"select Col1, sum(Col2)
where Col1 is not null
group by Col1
label Col1 'Month', sum(Col2)'Sum'", 0)```

```={{"Sheet1"; ARRAYFORMULA(IFERROR(VLOOKUP(INDIRECT("A2:A"&COUNTA(A1:A)), Sheet1!E3:F, 2, 0), ))},
{"Sheet2"; ARRAYFORMULA(IFERROR(VLOOKUP(INDIRECT("A2:A"&COUNTA(A1:A)), Sheet2!E3:F, 2, 0), ))},
{"Sheet3"; ARRAYFORMULA(IFERROR(VLOOKUP(INDIRECT("A2:A"&COUNTA(A1:A)), Sheet3!E3:F, 2, 0), ))},
{"Sheet4"; ARRAYFORMULA(IFERROR(VLOOKUP(INDIRECT("A2:A"&COUNTA(A1:A)), Sheet4!E3:F, 2, 0), ))},
{"Sheet5"; ARRAYFORMULA(IFERROR(VLOOKUP(INDIRECT("A2:A"&COUNTA(A1:A)), Sheet5!E3:F, 2, 0), ))}}``` ``````={QUERY({Sheet1!I2:I, Sheet1!L2:L; Sheet2!I2:I, Sheet2!L2:L},
"select Col1, sum(Col2)
where Col1 is not null
group by Col1
label Col1 'Month', sum(Col2)'Sum'", 0),
{"Sheet1"; QUERY(ARRAYFORMULA(IFERROR(VLOOKUP(QUERY(QUERY({Sheet1!I2:I, Sheet1!L2:L},
"select Col1, sum(Col2)
where Col1 is not null
group by Col1", 0),
"select Col1", 0),
{Sheet1!I2:I, Sheet1!L2:L}, 2), )),
"select Col1 where Col1 is not null", 0)},
{"Sheet2"; QUERY(ARRAYFORMULA(IFERROR(VLOOKUP(QUERY(QUERY({Sheet2!I2:I, Sheet2!L2:L},
"select Col1, sum(Col2)
where Col1 is not null
group by Col1", 0),
"select Col1", 0),
{Sheet2!I2:I, Sheet2!L2:L}, 2), )),
"select Col1 where Col1 is not null", 0)}}`````` • Wow! That works fantastically well, thank you! Jan 20, 2019 at 12:01
• I tried this with 2 sheets with the same months and it worked fine, but when I tried with more sheets including a sheet that had one month the others didn't have, it returned "Function ARRAY_ROW parameter 3 has mismatched row size. Expected: 6. Actual: 5." Is there a way to fix that? Jan 20, 2019 at 14:06
• @Rumbles this is the only fix: i.stack.imgur.com/wrwt3.png otherwise you will need to give up on "one cell" solution and run vlookup parts separately outside {}-constructed array Jan 20, 2019 at 14:25
• I've tried updating the part as you described, but it didn't work for me, I think I'm doing something wrong. I have sanitised the sheet, and removed the J and K columns on each customer sheet, so the ranges are a bit easier to work with. Here is a link for you to copy if you can? docs.google.com/spreadsheets/d/… Are you able to tell me what I have done wrong with the formula? Thanks Jan 20, 2019 at 15:48
• @Rumbles sheet1 outputs 6 rows of data while sheet2-5 outputs only 5 rows of data eg. to match 6:6 matrix you need to add extra empty row to sheet2-5 Jan 20, 2019 at 16:16

``````={{QUERY({Sheet1!I2:I, Sheet1!L2:L; Sheet2!I2:I, Sheet2!L2:L},
"select Col1, sum(Col2)
where Col1 is not null
group by Col1
label Col1 'Month', sum(Col2)'Sum'", 0),
{"Sheet1"; QUERY(ARRAYFORMULA(IFERROR(VLOOKUP(QUERY(QUERY({Sheet1!I2:I, Sheet1!L2:L},
"select Col1, sum(Col2)
where Col1 is not null
group by Col1", 0),
"select Col1", 0),
{Sheet1!I2:I, Sheet1!L2:L}, 2), )),
"select Col1 where Col1 is not null", 0)},
{"Sheet2"; QUERY(ARRAYFORMULA(IFERROR(VLOOKUP(QUERY(QUERY({Sheet2!I2:I, Sheet2!L2:L},
"select Col1, sum(Col2)
where Col1 is not null
group by Col1", 0),
"select Col1", 0),
{Sheet2!I2:I, Sheet2!L2:L}, 2), )),
"select Col1 where Col1 is not null", 0)}};
{{"Grand Total",SUM(QUERY({Sheet1!I2:I, Sheet1!L2:L; Sheet2!I2:I, Sheet2!L2:L},
"select sum(Col2)
where Col1 is not null
group by Col1", 0))},
SUM(QUERY(ARRAYFORMULA(IFERROR(VLOOKUP(QUERY(QUERY({Sheet1!I2:I, Sheet1!L2:L},
"select Col1, sum(Col2)
where Col1 is not null
group by Col1", 0),
"select Col1", 0),
{Sheet1!I2:I, Sheet1!L2:L}, 2), )),
"select Col1 where Col1 is not null", 0)),
SUM(QUERY(ARRAYFORMULA(IFERROR(VLOOKUP(QUERY(QUERY({Sheet2!I2:I, Sheet2!L2:L},
"select Col1, sum(Col2)
where Col1 is not null
group by Col1", 0),
"select Col1", 0),
{Sheet2!I2:I, Sheet2!L2:L}, 2), )),
"select Col1 where Col1 is not null", 0))}}`````` • for maximum perfection add this script, which is able to fetch sheet name(s) - usage:

• `=SHEET(2)` - returns name of the second sheet from left
• `=SHEET(1, TODAY())` - returns name of 1st sheet from left and formula should update daily
• `=SHEETLIST()` - returns list of sheet names and gid number (can be used with `TODAY()` too)

``````function SHEET(input) {
try {
if( (input>0) && (input <= sheets.length)) return sheets[(input-1)].getName() ;
else return "invalid sheet #" ;
}
catch( err ) {
return "#ERROR!"
}
}

``````function SHEETLIST() {
try {