external range in sumif formula in google spreadsheet

2
votes

in a google spreadsheet I want to use the formula SUMIF; the syntax of function is SUMIF(A1:A10,"Paid",B1:B10)

as first parameter (and last) I want use a range of another google sheet; I used the function IMPORTRANGE but the result is always #N/A

my code is:
sumif(importrange("xxhEwtMr2xxzRmiucxRgA5P119SEmsqL2R08gggt4Yyg", "list!$E:$E"),$S7,importrange("xxhEwtMr2xxzRmiucxRgA5P119SEmsqL2R08gggt4Yyg","list!$C:$C"))

where am I wrong?

ps. the error pop-up report:
Error Argument must be a range.

2
google-sheetsspreadsheetgoogle-docs

2 Answers

1
votes

My understanding is that the third argument in the SUMIF must be a direct reference to the local worksheet.

You can either add an additional tab to import the data into, or try a different formula approach such as:

=SUM(QUERY(importrange("xxhEwtMr2xxzRmiucxRgA5P119SEmsqL2R08gggt4Yyg", "list!$C:$E"),"select Col1 where Col3 = '"&$S7&"'",0))

0
votes

You could make an easier work around is to use the import range to a new sheet. Then sumif it from that sheet. It will still dynamically update.