When making references to a range from one Google Sheets into another Google Sheets, you will need the Google Sheets IMPORTRANGE function. If you need to count the occurrences of a certain string of characters or any cell content that meet certain criteria, you may need the Google Sheets COUNTIF function. Combining the two formulas gives you the superpower of counting over an external range based on a criterion. However, there is a catch!
How to Use the Range from One Google Sheet Into Another
The formula is quite simple to import a range from another Google Sheets. Here are a couple of examples from the official help article from Google on IMPORTRANGE:
You Must Explicitly Grant Permission To Your Spreadsheet!
According to the Google Editors Help article referenced above, you have to allow your spreadsheet to pull data from one document into another.
Spreadsheets must be explicitly granted permission to pull data from other spreadsheets using
IMPORTRANGE. The first time the destination sheet pulls data from a new source sheet, the user will be prompted to grant permission. Once access is granted, any editor on the destination spreadsheet can use
IMPORTRANGEto pull from any part of the source spreadsheet. The access remains in effect until the user who granted access is removed from the source.
If the data you are trying to import is too large, you may get an error.
How To Use the COUNTIF Function Over an Google Sheets Imported Range
Using the COUNTIF function over an imported range is as simple as embedding the IMPORTRANGE function into the COUNTIF formula and there you have it! You can learn the details of how to use the COUNTIF function in the official Google Docs Editor Help article.
How to Combine COUNTIF and IMPORTRANGE
This seems like a straightforward combination at first: just replace the range in the COUNTIF formula with the entire formula as in the example blow for IMPORTRANGE and you would be done.
=COUNTIF(IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A1:C10"), criterion)
Nope! There is a catch! And that’s probably why you landing on this page to start with.
Why is Combining COUNTIF and IMPORTRANGE Returning 0 Even When the Formula Is Correct?
Google Sheets has a little problem that hopefully they can notice and fix soon. When embedding the IMPORTRANGE inside another formula, you may not get a prompt to allow access to you current spreadsheet to pull data from the source spreadsheet. This prompt currently only shows up if you first use the IMPORTRANCE formula by itself; use if in a separate cell without embedding it into another formula.
You should then get the prompt to Allow Access, only then can you use IMPORTRANGE as part of another forumla and have it return the correct values.