I'm building a complex Google Sheets document and for one sheet I use the function IMPORTRANGE. It's very handy and imports new rows from another sheet when they are added. The problem is this:

After the last row of the import from my IMPORTRANGE I add a small 'Total' calculation to SUM all of the above numbers.

What I want is that the 'total' calculation row moves down when the IMPORTRANGE expands with more rows.

Is there any way to achieve this dynamically / automatically so that my 'Total' calculation moves down and keeps summing?

2 Answers 2


Use the Google Sheets array handling feature


Lets assume that A1 has the spreadsheet key required by IMPORTRANGE, that eh source spreadsheet has only the rows required to hold the data (no blank rows) and that the formula is set in A2


See the wiki for references and related questions.

  • If you have problems with the above formula create a couple of demo spreadsheets, one to to hold the data to imported and the other to import the data and add the link to the question. Jan 8, 2020 at 15:08

If I understand your question correctly (i.e. that you have a source Sheet in which the SUM lives in row X and if you add rows above it then your IMPORTRANGE call will not follow along (as it would if it was in the same document) and subsequently you lose the reference to that SUM cell) then one way around this is to give that SUM cell a name (right-click on the cell and select Define named range from the menu then label the cell MY_SUM (or whatever you wish to name it).

Once that's done you can then reference it in the IMPORTRANGE by its name like:

=IMPORTRANGE(URL, "'sheet name'!MY_SUM")

Now if you insert rows above that cell it doesn't matter and the reference will persist.

Your Answer

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

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