Home->Tutorials->Google Sheet help->
Conditional sum of data from another google spreadsheet file using FILTER and IMPORTRANGE function
[Updated as on Feb 21, 2021]
Using SUMIF function along with IMPORTRANGE function to get the conditional sum of data from another google spreadsheet result in error message as shown below:
One way to solve this issue is to copy data from the columns containing the data for which sum is required and the column which contains the criteria from the other google spreadsheet to the spreadsheet where SUMIF function has to be used using import range function. This requires the entire data from the source file to be replicated in a new blank sheet before applying SUMIF function.
However there is an easy method to get conditional sum by combining SUM function with FILTER function. The syntax is as follows:
=SUM(FILTER(IMPORTRANGE("url","Sheet name and range of the column containing data"),IMPORTRANGE("url"," Sheet name and range of the column containing the criteria")=criteria))
Lets use an example to see its application:
This is a spreadsheet file which contains sales details of three products A, B & C.
This is another spreadsheet file where product wise sales summary has to be generated in the table as follows:
Now the formula can be applied in cell E6 in the syntax given above:
Here url of the google spreadsheet which contains the product wise sales details = https://docs.google.com/spreadsheets/d/1inXvCXy05ioAf-dVkwk9Bz1g8O90uxuwSHeULACKty4/edit#gid=0.
Sheet name and range of column which contains the sale figures = Sheet1!d3:d12
Sheet name and range of column which contains the criteria i.e Product name = Sheet1!e3:e12
Now press enter and drag down the formula. Lol! The required sales summary is ready.
Page views: