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 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.
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.
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.
Sometimes you wish you could see how your site comes up on Google in other countries. There is a tool for that called isearchfrom.com and it sounds like an item to add to your toolbox, tech friends! Jozsef Torsan has a whole article about it.
I’ve found a great tool (isearchfrom.com) recently that emulates the Google search location. You have to enter the country where you search from, the language and you can even specify the device. Then you enter the words you are searching for and click on Search. You will get exactly the same search results if you were in the specified country.
What is it good for?
If you work on SEO and want people to find your website not just in your country, but in other countries as well, then you can check the Google search result position in the different countries. Since I started to deal with the SEO of Bookmark Ninja I’ve been using this tool regularly. And it’s good for not just checking the organic search results but you can also check how your AdWords ads show up in a search.
Have you ever heard of the term “idiot box?” Maybe they are not that dumb? Smart TV’s are able to watch you watch TV and learn about the devices on the same network in your home. By the way, even if you had never heard of the idiot box term, I am sure you can easily guess that it is the North American word for a television set. At least, that is what Google seems to believe.
In fact, search of “define idiot box” yielded the definition “a television set.” Marriam-Webster seems to agree as well.
Back to our point, though. Your Smart TV has been reported to spying on you with your consent but, if you are like the majority of Smart TV owners, without your knowledge. So, go ahead and read up on these stories about what Samba TV does with your data and what bad hackers could trick your device into doing. Armed with this knowledge, you will at least be able to understand how in the world did that company know to advertise this or the other product to you.
Yes, an extra dot in the username part of the email address does not change who gets the email address at Gmail.com. Please be careful to notice that this might not be true of all other email service providers.
For example: firstname.lastname@example.org is the same as email@example.com or any variation of the position or number of dots before the @ sign. If someone tries to open a new Gmail account with just a dot as a difference between their address and yours, Google will tell them the username already exists.
Caution: if you used Gmail through an organization like school, business, or company, your dots do matter.
Just as as announced in June 2017, Google just released this July 12 2017 a brand new product called Backup and Sync. In addition to brand new functions like backing up pictures and files from USB connected media, this tool comes to gather the functionalities that were present in Google Photos and Google Drive into one product.
Backup and Sync for Google Photos and Google Drive is available for Mac and Windows comes to move your clutter from your desktop into the cloud. Backup and Sync can be customized to only sync certain folders, to ask you what and where to delete files, and so forth. You can read more about it
With that said, if you are an Enterprise, Business, Education or Nonprofit user, Google made sure to mention that Backup and Sync is not for G Suite users just yet. These will need to wait for Drive File Stream (to be released to “soon” to the public as of the publication of this article).
Google Backup and Sync for Mac
Some Security Considerations
Having all your data stored on the cloud could be a good or a dangerous thing, though. If you already used Google Drive or Google Photos you most likely understand the risks attached to having your life in the cyber cloud. The risks include having all your data stolen by anyone with your password if the password is your last line of defense. So, if you are going to use this or any of the cloud storage of personal or corporate data that is not for public release, please add 2-step authentication as part of your security practices. We would recommend it for all of your accounts where possible.