How to Importrange + CountIf on Google Sheets

Question

I need to import information from another google sheet, COUNTIF that information under the criteria of one column saying "VIL" and another column saying "Pass"

I am using variations of the below: =countifs((IMPORTRANGE(E65, "Sheet1!A2:H200")),$E$2:$E$30,"Passed")

Where I am trying to have it display the count of boxes that have "Passed" from the other google sheet in E65. I am only getting error messages.

Any help on this would be appreciated!

Answer 1

run this first to connect your sheets:

=IMPORTRANGE(E65; "Sheet1!A2")

then try:

=ROWS(QUERY({IMPORTRANGE(E65; "Sheet1!A2:H200")}; 
 "where Col5 = 'Pass' and Col1 = 'VIL'"; ))

change Col1 to where the VIL is