1

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.

2
  • I would rather not, is there some information I missed to help answer this question?
    – Rumbles
    Jan 19, 2019 at 18:43
  • 1
    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

3 Answers 3

1

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

enter image description here

__________________________________________________________

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

1

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

1

10
  • 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
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)}};
 {{"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))}}

1


  • 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!" } }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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