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

Question

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? — Rumbles, 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) — user0, Jan 19, 2019 at 20:06

user0 · Accepted Answer · 2019-01-21 21:21:36Z

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), ))}}

demo spreadsheet with test area

__________________________________________________________

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), ))}}

demo spreadsheet

user0 · Accepted Answer · 2019-01-19 20:18:46Z

1

={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)}}

edited Jan 19, 2019 at 20:18

answered Jan 19, 2019 at 19:42

user0

26k8 gold badges49 silver badges77 bronze badges

Wow! That works fantastically well, thank you!
– Rumbles
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?
– Rumbles
Jan 20, 2019 at 14:06
1

@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
– user0
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
– Rumbles
Jan 20, 2019 at 15:48
1

@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
– user0
Jan 20, 2019 at 16:16

| Show 5 more comments

user0 · Accepted Answer · 2019-01-20 17:38:54Z

={{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 {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets() ;
  if( (input>0) && (input <= sheets.length)) return sheets[(input-1)].getName() ;
  else return "invalid sheet #" ;
}
catch( err ) {
  return "#ERROR!" 
}
}

function SHEETLIST() {
try {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets()
  var out = new Array( sheets.length+1 ) ;
  out[0] = [ "NAME" , "#GID" ];
  for (var i = 1 ; i < sheets.length+1 ; i++ ) out[i] = 
  [sheets[i-1].getName() , sheets[i-1].getSheetId() ];
  return out
}
catch( err ) {
  return "#ERROR!" 
}
}

Stack Exchange Network

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

3 Answers 3

one cell solution:

demo spreadsheet with test area

__________________________________________________________

2-cell solution:

demo spreadsheet

Your Answer

Not the answer you're looking for? Browse other questions tagged
google-sheets
formulas
google-sheets-query
google-sheets-arrayformula
vlookup
or ask your own question.

Hot Network Questions

3 Answers 3

one cell solution:

__________________________________________________________

2-cell solution:

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged google-sheetsformulasgoogle-sheets-querygoogle-sheets-arrayformulavlookup or ask your own question.

Related

Not the answer you're looking for? Browse other questions tagged
google-sheets
formulas
google-sheets-query
google-sheets-arrayformula
vlookup
or ask your own question.