Google-sheets – Adding cells from multiple Google Sheets into a “master” sheet

google sheets

I have 20 Google Sheets, not 20 tabs in 1 sheet, but 20 separate files each with 3 tabs called Identify | Protect | Detect.

I would like to add or collate the numbers from each of these separate files into 1.

For instance, put the sum all the numbers from C41 in each of the 20 sheets into cell C41 of the "master" spreadsheet

In excel you could just use:

=SUM('file_path\[file1.xls]sheet1!$C$41)+('file_path\[file2.xls]sheet1!$C$41)

All the Google Sheets live in the same Google Drive directory

Best Answer

you need to use =IMPORTRANGE for that... 20 times and wrap it in =SUM

this is how =IMPORTRANGE works:

=IMPORTRANGE("1W8yfNmNBBZuEKqk56k-ZxaKrLOiGOBASrkRCQNVxOSA"; "Sheet1!A1")

where the "code" from first quotes is ID of the spreadsheet (which can be found in URL of the particular spreadsheet) and 2nd phrase in the quotes is a combination of sheet name and cell reference/range.

the ID 1W8yfNmNBBZuEKqk56k-ZxaKrLOiGOBASrkRCQNVxOSA is from: https://docs.google.com/spreadsheets/d/1W8yfNmNBBZuEKqk56k-ZxaKrLOiGOBASrkRCQNVxOSA/edit

so basically you need to modify this formula by adding ID and sheet name:

=SUM(IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41");
     IMPORTRANGE("ID"; "'sheet name'!C41"))

NOTE: you may be prompted to allow connection between those spreadsheets of yours...

Related Topic