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:

IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A1:C10")

IMPORTRANGE(A2,"B2")

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 IMPORTRANGE to 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.

Example:

COUNTIF(range, criterion)

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.

Posted by Rafiki Technology

We learned a ton in school, on the job, but also from great technical insights that others shared on various platforms. We are just giving it back and glorifying Jesus Christ, the Inventor of all human beings. Please note that all information shared on or through our site is of good faith and is not intended to cause any harm individuals, groups, organizations, or devices. Just to be clear: you assume all responsibility for anything you do; we are not liable for anything that should go wrong.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.