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.

Have you tried to sum that? – Jacob Jan Tuinstra May 6 '13 at 20:09 — Jacob Jan Tuinstra, May 6 '13 at 20:09
Frank, see chat.stackexchange.com/rooms/7/google-fu – Jacob Jan Tuinstra May 7 '13 at 11:40 — Jacob Jan Tuinstra, May 7 '13 at 11:40

Community · Accepted Answer · 2017-04-13 12:57:17Z

2

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

Community♦

1

answered May 7 '13 at 6:45

18.4k12 gold badges72 silver badges161 bronze badges

add a comment |

Community · Accepted Answer · 2017-05-23 12:35:00Z

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

At Spreadsheet 1 sum up the values you want to a specific cell (e.g. A4)
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 -
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

BTW: I had to use =ImportRange("key";"SheetName!CellName") to make it work. (using a ";" instead of a ",") and it doesn't work yet with new google sheets. — StampedeXV, Mar 3 '14 at 16:36

paulhastings0 · Accepted Answer · 2019-03-27 20:42:06Z

I ran into a similar issue yesterday. Solution was:

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

2 important notes:

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.
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.

3 Answers 3