How to SUM() a column from one Google Spreadsheets in another spreadsheet

Question

Has anyone been able to return the SUM() result of a column in one Google Spreadsheets to a different spreadsheet?

I am trying the ImportRange(key, criteria) but I am not having any success. I am just bringing in the entire column and not the sum result.

Answer 1

If you want to leave the data on the IMPORT sheet untouched, then you need to do this:

=SUM(IMPORTRANGE("key","range"))

This is a good post, here on Web Applications, about the IMPORTRANGE function: IMPORTRANGE

Answer 2

ImportRange(...) is indeed the function you need to use. Here are the steps you need to follow:

1. At Spreadsheet 1 sum up the values you want to a specific cell (e.g. A4)
2. On the address bar of Spreadsheet 1 check for the key value (it's the long number that starts after key= and ends before #gid) - e.g. key=ABC -
3. At spreadsheet 2 go to the cell to which you need to present the sum and write =ImportRange("key","SheetName!CellName"), e.g. based on our example =ImportRange("ABC","Sheet1!A4")

You can also refer to this post ImportRange

Answer 3

I ran into a similar issue yesterday. Solution was:

=SUM(IMPORTRANGE("SpreadsheetURL","SheetName!A1:A100"))

2 important notes:

1. You will initially see a #REF! error in your cell with a red color in the corner. You'll need to hover over it and allow access to the spreadsheets that you're referencing.

2. If any of the original data in the spreadsheet range you're referencing in A1:A100 contain errors such as

#NULL!
#DIV/0!
#VALUE!
#REF!
#NAME?
#NUM!
#N/A

then your formula must be modified to include SUMIF() as follows

=SUMIF(IMPORTRANGE("SpreadsheetURL","SheetName!A1:A100"), ">0")

This ensures that it's only adding actual numbers and not error codes.

Answer 4

You can access a cell or a range from another google sheet (i mean a separate file) using the the IMPORTRANGE function. Here is an example:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Aviydfsh6PygtPjphKc5Fnvnm8g_Xoedf6utNqp2Ql4A", "MySheetName!E107") + SUM(E4:E99)

Here we provide the url to the other sheet in the IMPORTRANGE function, fetches cell E107 from sheet named MySheetName and adds it with sum of range E4:E99 in current sheet. While doing this you'll be asked to allow current sheet to access the other sheet.