Search code examples
google-sheetsgoogle-sheets-formulagoogle-sheets-query

Google Sheets - Set range until the end of column & set each column is the sum of 3 columns

I created a Google sheet containing 2 sheets(tabs).

The first sheet table is auto-created where it grabbing some data from my Google Ads.

The 2nd sheet table is where I manually created. Now,

1) I want the date row to auto-fill from the 1st sheet, but how can I set the range from the first column until the end of the 1st sheet column? Because now I set it to =QUERY(Sheet1!B2:Sheet1!F2), but tomorrow Sheet 1 column gonna be until G, and I need to manually set the range to G2.

2) Under the Impression row, I want each column to sum 3 columns from Sheet 1. Meaning the first column formula gonna be =SUM(Sheet1!B3:Sheet1!D3), the 2nd column gonna be =SUM(Sheet1!E3:Sheet1!G3) and so on until the end of Sheet 1 column. But what happens now is =SUM(Sheet1!B3:Sheet1!D3), =SUM(Sheet1!C3:Sheet1!E3) and so on.

Here's an example https://docs.google.com/spreadsheets/d/19BvG1inXgYfJxJ9mJf-TmkNxotleW3WqJIivzq0SNFk/edit?usp=sharing

Really hope some data analyst guru/sheet guru can give me some insights.

Solution

  • instead of your QUERY formula on sheet2 use this in B2 cell:

    =QUERY(Sheet1!B2:2)

    instead of your SUM formula on sheet2 use this in B3 cell and drag to the right:

    =IF(LEN(B2), SUM(INDIRECT("Sheet1!"&ADDRESS(3, COLUMN(), 4)&":"&
                                    ADDRESS(3, COLUMN()+2, 4))), )

    0