CODEWITHSUNDEEP
google-sheetssumgoogle-sheets-formulagoogle-sheets-queryimportrange
Umesha Gunasinghe
Umesha Gunasinghe

Reputation: 779

How to connect two google sheet workbooks using SUMIFS?

I have the following formula

SUMIFS(Sheet1!I2:I12,Sheet1!H2:H12,"1111-1111",Sheet1!B2:B12,"1111")

I want the Sheet1 from a separate workbook (not sheets in the same workbook), how can I do this?

My workbook1 is https://docs.google.com/spreadsheets/d/1L4t2xkmHPAOn8uGT3bFy56UMWrZaHsyq4FlOh6uR2g0/edit?usp=sharing

I want to do following in workbook2

= SUMIFS(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1VayuaMlSMvkhBKKuQnRTuTwk6kshFWQanWxa5KWh2ag","Sheet1!I2:I12"),IMPORTRANGE("https://docs.google.com/spreadsheets/d/1VayuaMlSMvkhBKKuQnRTuTwk6kshFWQanWxa5KWh2ag", "Sheet1!H2:H12"),"1111-1111",IMPORTRANGE("https://docs.google.com/spreadsheets/d/1VayuaMlSMvkhBKKuQnRTuTwk6kshFWQanWxa5KWh2ag", "Sheet1!B2:B12"),"1111")

Upvotes: 1

Views: 8099

Answers (3)

player0
player0

Reputation: 1

use:

=QUERY(IMPORTRANGE("1L4t2xkmHPAOn8uGT3bFy56UMWrZaHsyq4FlOh6uR2g0", "Sheet1!A:I"), 
 "select sum(Col9) 
  where Col8 = '1111-1111' 
    and Col2 = 1111 
  label sum(Col9)''")

enter image description here

Upvotes: 2

confuseddesk
confuseddesk

Reputation: 85

I think you need to use IMPORTRANGE to bring in the columns you want to your active sheet. You can either import the data first and then use a formula in another cell to work with it, or you can use IMPORTRANGE inside your SUMIF formula.

EDIT: Here's a workbook that has it working.

The formula in Sheet1!A1 is =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1L4t2xkmHPAOn8uGT3bFy56UMWrZaHsyq4FlOh6uR2g0/edit?usp=sharing","Sheet1!A1:I12"), which pulls the data from your shared workbook1 into this sheet.

The formula in Sheet2!A2 is =SUMIFS(Sheet1!I2:I12,Sheet1!H2:H12,"1111-1111",Sheet1!B2:B12,"1111")

You could also put the SUMIFS formula on Sheet1 if you prefer. It's 2 steps instead of one, but it works!

Upvotes: 0

kaitlynmm569
kaitlynmm569

Reputation: 1715

You can use the IMPORTRANGE formula to pull data from another sheet.
You can either embed the IMPORTRANGE in your formula in order to pull directly from it, or you can use the formula on its own in a new sheet, and pull from the sheet located within the same Spreadsheet for ease of access and a shorter formula overall.
Updating the spreadsheet you are pulling from will automatically update the data coming from the IMPORTRANGE formula.

For example:

example

A1 contains the basic formula =importrange("https://docs.google.com/spreadsheets/d/1I8BiB-2gSFueLIKiGsMY-URLOpnB8QzbUrx8/edit#gid=0", "Sheet1!I1:I12")

while B1 contains =sum(importrange("https://docs.google.com/spreadsheets/d/1I8BiB-2gSFueLIKiGsMY-URLOpnB8QzbUrx8/edit#gid=0", "Sheet1!I1:I12"))

Make sure you replace the URLs above with the one for the sheet you are trying to pull from

Upvotes: 0

Related Questions