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.
If you are technically inclined, feel free to report the abuse to GoDaddy’s abuse form found in the footer or their website. As of today, the direct link to the abuse form looks like this: https://supportcenter.godaddy.com/AbuseReport.
If you are not technically inclined or if you just want to pay your GoDaddy bill, do not follow any links from that email, just open your browser and login to your GoDaddy account directly and proceed to managing your billing from within your account.
Did you get a text message stating that your Debit Card was locked? Well, do not fall for it. I may look many different ways including the following:
FROM:Debit Card Alert. Call 844-307-2969 Now MSG:0000 New Message! Your Debit Card is Locked
Best response here is to NOT call the number in this message, but rather to call your Debit card company directly to confirm using the number on the back of your card. They will then tell you what to do.
As painful as it is to announce this, what some consider the 5th largest domain registration company in the world has been breached. Or should we call it a cybersecurity incident as they claim in their mea culpa, comforting customers that, “No credit card data was compromised as a result of this incident?” (Emphasis ours). The mea culpa is posted on a subdomain of their website: https://notice.web.com/ where they publish the following FAQ.
Frequently Asked Questions
What happened? On October 16, 2019, Web.com determined that a third-party gained unauthorized access to a limited number of our computer systems in late August 2019, and as a result, account information may have been accessed. No credit card data was compromised as a result of this incident.
What are you doing about it? Upon discovery, we took immediate steps to stop the intrusion. We promptly engaged a leading independent cybersecurity firm to investigate and determine the scope of the incident. We notified the proper authorities and began working with federal law enforcement.We are notifying affected customers through email and via our website, and as an additional precaution are requiring all users to reset their account passwords.
What data/information was involved? Our investigation indicates that account information for current and former Web.com customers may have been accessed. This information includes contact details such as name, address, phone numbers, email address and information about the services that we offer to a given account holder.We encrypt credit card numbers and no credit card data was compromised as a result of this incident.
What can I do to protect my account? We have already taken additional steps to secure your account, and there is nothing you need to do at this time. The next time you log in to your account you will be required to reset your password.As with any online service or platform, it is also good security practice to change your password often and use a unique password for each service.
Is my credit card information at risk? We store credit card numbers in a PCI (Payment Card Industry) compliant encryption standard and do not believe your credit card information is vulnerable as a specific result of this incident. That said, it is good practice to monitor your credit card account and we encourage you to notify your credit card provider if you see any suspicious charges.
Should I reset my password as part of this? We have already taken additional steps to secure your account, and there is nothing you need to do at this time. The next time you log in to your account you will be required to reset your password.As with any online service or platform, it is good security practice to change your password often and use a unique password for each service.
FAQ on the Important Safety information at notice.web.com
KrebsOnSecurity was among the first if not the first to break the news to the public in an article published on October 30th. According to Krebs.
Web.com encourages customers to call them with any questions. The phone number is available on the notice page they published.
Our word of advice here: please do not reuse passwords and setup multifactor authentication where possible.
Where Can I Learn Regular Expressions Syntax and Use?
Regular expressions are fun, if you can figure out how to use use them of course. So, let’s fix that. Well, even better, just go fish on your own starting with Regular-Expressions. I kinda like how colorful that site is!
Where Can I Test My Regular Expressions?
If you are learning Regular Expressions, you probably wonder sometimes if there is a way to make sure what you have in mind will work. Well, there are several ways of testing your regex, but I have found Regex Tester to be pretty easy to use.
If you are a MacOS user, you most likely have a pop up message on your mac recently stating, “This app will not work with future versions of macOS and needs to be updated to improve compatibility. Contact the developer for more information.
Despite the fact that the frequency of those messages is a bit exasperating, you need to pay attention to the issue they raise.
Is There Anything I Need To Do About Mac OS Transition To 64-bit Only OS?
It is imperative that you make sure your essential applications are compatible with future versions of macOS. Applications like your word processor, your code editor, your remote desktop application, your virtual machine platform are only a few examples you need to verify they are compatible.
If you use your Mac for making, producing, editing, and potentially even playing music, this is a very important notification for you. Please make sure your application is 64-bit compatible or do not upgrade to any macOS past Mojave until your applications are 64-bit ready. Pro-Tools-Experts suggests a list of press releases and announcements from music application vendors and producers warning their users not to upgrade to macOS Catalina just yet.
How Do I Find Out Which Applications Are 32-bit or 64-bit On My Mac?
The process is quite easy to find out which applications on your Mac are 32-bit or 64-bit. In a helpful guide, Apple suggests:
From the Apple menu, choose About This Mac,
then click the System Report button.
From the system report, scroll down to Software in the sidebar,
then select Applications.
When you select an individual application, you will see a field titled 64-bit (Intel). “Yes” indicates 64-bit; “No” indicates 32-bit.
If you’re using macOS Mojave, select Legacy Software in the sidebar to see all applications that have not been updated to use 64-bit processes.
We hope that you will now prepare for the new all 64-bit era with more confidence! Please ask us directly if any questions on Twitter @RafikiTechno or directly on this blog through our contact form.