How to use Sumif using range from a complete different Googlesheet/doc

Question

I know how sumif works when I need to access it within the same Google "workbook" (using the analogy from excel). By workbook I mean a collection of sheets, not sure whether there is a different way to refer to Google workbook.

For example in the sheet (Example 3): https://docs.google.com/spreadsheets/d/1Dm-N-1X38zHartE3JbPUtWDnYwEpkGHl6v06huvjSa8/edit#gid=0

I have Sheet2, with column A contain strings and column B containing numerical value. On sheet 1, I have a sumif function which can be query data stored in Sheet2, and sum the cells which match A1 in Sheet1.

The problem starts happening when I try to refer to ranges in a completely different workbook, which is shown below.

I am trying to do a sumif over 2 ranges from a different "workbook". The data is stored here (Example 2): https://docs.google.com/spreadsheets/d/1P5Inf09fLSRmsGbG7LwlE4V-r7DzqY0SB5tJuMKMZH0/edit#gid=0

The Sumif function is in Cell B1 of the following sheet (Example 1):https://docs.google.com/spreadsheets/d/1AitilELd6w7Dbv9d-mKhBYGTBaO6DdkU29Y5mofX2TI/edit#gid=0.

From my understanding importrange is typically used to refer to ranges in completely different workbooks, as a result I use importrange as the first and last arguments in the sumif function in the Sheet Example 1.

What am I doing wrong? Why is this not working?

Can anybody help?

Thanks a lot

Ed Nelson · Accepted Answer · 2016-10-30 02:12:31Z

3

See if this query does what you want:

=SUM(query( QUERY( Importrange("1P5Inf09fLSRmsGbG7LwlE4V-r7DzqY0SB5tJuMKMZH0","Sheet1!A1:B10") ) , "select Col2 where Col1 contains '"&A1&"'" ) )

answered Oct 30, 2016 at 2:12

Ed Nelson

10.4k2 gold badges32 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

alpha_989 Over a year ago

Thank you very much, Ed. I actually got it to work with the Vlookup function.. but the query option looks much more powerful. I looked into it a bit and seems like it involves the Google Visualization language.. so I will look into that when I have a bit of time. Sticking this for future reference.

Collectives™ on Stack Overflow

How to use Sumif using range from a complete different Googlesheet/doc

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related