COUNTIF and IMPORTRANGE returning 0

Question

I would like to use the COUNTIF and IMPORTRANGE functions in Google Sheets, but the results keep returning 0. Here's my formula:

=COUNTIF(IMPORTRANGE("sheet key","PDFs!A2:A13"), "Yes")

There are no permission restrictions on the sheet I am trying to query, though I might restrict it to specific people.

I've tried the formula using the key and the sheet URL.

I've tested the formula with a tab in the same sheet and it works. Here is the formula I used:

 =COUNTIF(PDFs!A2:A13,"Yes")

Any thoughts on why the COUNTIF/IMPORTRANGE formula isn't working?

Would restricting permissions make the formula not work?

Solution

Yes, specific authorisation is necessary and no, you don't need the sheet name as well as the key.

Try just:

 =IMPORTRANGE("sheet key","A2:A13")

which should ask you for authorisation (if required) and grant it (if necessary). Then replace above with:

=COUNTIF(IMPORTRANGE("sheet key","A2:A13"),"Yes")

Comments

[kjb]

Thank you, pnuts! I did as you suggested and successfully connected the sheets so already much better. However - When I use =IMPORTRANGE("key","PDF!A2:A13") I get the following error: Array result was not expanded because it would overwrite data in B4. If I remove the quotes around the range, to look like =IMPORTRANGE("key",PDF!A2:A13) I get the following error: Cannot find range or sheet for imported range. Did I miss something?

[New_2_Code]

Import range will expand out as far as the data requires. If there is data in a cell where the importrange will try to put the data you will get the above error. Solution is to remove and data in the way of the importrange.