google-sheetssumgoogle-sheets-formulagoogle-sheets-queryimportrange

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")
Solution

  • use:

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

    enter image description here