Google Sheets – SUM Columns from Other Sheets and Group by Month

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryvlookup

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.

Best Answer

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

Related Topic