Sumif (conditional sum) between two different Google Sheets files. That’s what I meant by Sumif Importrange in Google Sheets.

Importrange is the name of a function in Google Sheets to import data from one Google Sheets file to another Google Sheets file.

You May Like Reading: Google Sheets Importrange Function – Basic to Advanced Use Tips.

Google added this function to make available data from one Google Sheets file to another Google Sheets file (import data). It doesn’t have any other role.

I mean the Importrange function lacks data manipulation capability. But there are functions that work well in combination with the Importrange function in Google Sheets. A simple example is Sum.

=sum(importrange("URL","Detail!C1:C"))

Can We Use the Sumif Function to Sum an Imported Range?

Yes! If the imported range is standalone, it will work without any issue. But if you use the Importrange formula within Sumif in Google Sheets, it will return #N/A.

Checking the tooltip will popup the most annoying Sumif error, i.e. the “Argument must be a range”.

So in the latter case, instead, Sumif importrange in Google Sheets, use the Query importrange combo.

You May Like Reading: Learn Query Function with Examples in Google Sheets.

What Is the Cause of the “Argument Must Be a Range Error” in Sumif?

As per Sumif syntax SUMIF(range, criterion, [sum_range]), the sum_range must be a physical range like A1:A10 not a virtual range (a sum_range generated by another formula).

Needless to say, this Sumif error makes many Sumif users left with no options other than switching to use Query in Google Sheets. I’ll shed some light on this in the last part.

Example to Sumif in an Imported Range in Google Sheets

In a Sumif Importrange formula in Google Sheets, there will, of course, be a minimum of two files involved.

In my example there are two files and here are the file names and sheet (tab) names in that files.

Source File:

File Name: OS_Liability; Tab Name: Detail.

Range to Import: A1:D in the Detail tab (data table below).

Destination File:

File Name: OS_Summary; Tab Name: Summary and Sumif_Import.

Sumif on Imported Data in Google Sheets

Let’s first import the data from OS_Liability using an Importrange formula to the OS_Summary.

The formula to use in cell Summary!A1 in the OS_Summary tab is below. For the first time import, you may require import access. Click “Allow access” upon prompt.

=importrange("insert URL","Detail!A1:D")
Note: Replace the string insert URL with the URL of the file OS_Liability.

Then in the next tab in the OS_Summary which is Sumif_Import follow the below instructions.

• Type the customer name “A” in cell A1 and “B” in cell A2.
• Then use the following Sumif formula in cell B1.

=sumif(Summary!A:A,A2,Summary!C:C)

Copy and paste the formula to cell B2.

This is one example of the use of Sumif Importrange in Google Sheets. In this method, we have used both the functions independently.

Can We Use a Single Sumif Formula to Include Multiple Criteria?

Yes, no doubt we can do the same with the help of an ArrayFormula!

I just don’t want to use multiple Sumif for multiple criteria as above.

In my example, there are just two criteria. If the number of criteria is large, for example in 10 rows, there will be 10 Sumif formulas.

I don’t like such a scenario as it may reduce the performance of my cloud-stored Google Sheets file.

I normally prefer a Sumif array as below in cell B1 which expands to B2.

=ArrayFormula(sumif(Summary!A:A,A1:A2,Summary!C:C))

Can you identify the differences?

The criterion (A1) cell changed to criteria (A1:A2) range. To support the range used the ArrayFormula function.

Sumif Importrange Using Query in Google Sheets

In the just above example, I have imported the data in one tab and used the Sumif in another. This is because Sumif doesn’t support the Importrange formula in sum_range as below.

=sumif(importrange("insert URL","Detail!A1:A"),A1,importrange("insert URL","Detail!C1:C"))

I just don’t like to use a separate tab for the imported data. Is there any alternative to use the imported data and the Sumif together?

If you use a Query, you can avoid using the imported data separately. Now let’s think about achieving the same result, i.e Sumif Importrange in Google Sheets, using a single formula.

Let’s delete the Summary tab. In cell B2 in Sumif_Import, use the following Query.

=query(importrange("insert URL","Detail!A1:D"),"Select sum(Col3) where Col1='"&A1&"' label sum(Col3)''")

Copy-paste to B2.

What about a Query Sumif Similar Array Formula for Cell B1 which Expands to B2?

Most users will satisfy with the group and sum feature of Query.

=query(importrange("insert URL","Detail!A1:D"),"Select Col1, sum(Col3) where Col1 is not null group by Col1")

It will ignore your criteria in column A.

If you are very much particular to get the summary against the manually entered criteria in column A, use the Vlookup with the Query Importrange above.

You May Also Like Reading: Vlookup in Google Sheets – 10 Formula Variations, Tips, and Tricks.

Generic Formula – Sumif Importrange Arrya Formula Alternative in Google Sheets

ArrayFormula(Vlookup(criteria,query_importrange,index,is_sort))

The Sumif Importrange array formula alternative in cell B1 which will auto expand to cell B2:

=ArrayFormula(VLOOKUP(A1:A2,query(importrange("insert URL","Detail!A1:D"),"Select Col1, sum(Col3) where Col1 is not null group by Col1"),2,0))

I hope this makes sense?

Still, having any doubt about Sumif Importrange in Google Sheets? Please refer to the below additional resources and also drop your queries and views in the comments.

Additional SUMIF Importrange Resources

1. How to Vlookup Importrange in Google Sheets.
2. Combined Use of Sumif with Vlookup in Google Sheets.
3. Multiple Criteria Sumif Formula in Google Sheets.
4. Dynamic Column in Vlookup Importrange Formula in Google Sheets.

There a plenty of other tutorials related to Vlookup, Sumif, Query, and Importrange in this blog. Use the search icon on the navigation bar or the footer search field.

I have only linked a few tutorials relevant to this tutorial.