Category: Little Tips

Image: Snippet from Help Article - IMPORTRANGE Imports a range of cells from a specified spreadsheet.
GoogleLittle Tips

Google Sheets CountIf and ImportRange Return 0 and Won’t Offer to Allow Access

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.

Little TipsPhishingSecurity

Your Debit Card Is Locked Scam

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.

You can thank me later. 🙂

Your bank most likely has some guidelines on their sites about what to do. For example, here is what Chase advises their customers: https://www.chase.com/digital/resources/privacy-security/questions/fraud

Little TipsprogrammingWeb Development

Where Can I Learn Regular Expressions? How About Testing?

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.

Et voilà! Look at you! All equipped and ready!

Apple TroubleshootingLittle TipsmacbookOS XWeb Development

What Does It Mean That “App” Is Not Optimized For Your Mac?

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.

Image from apple website containing the message: "App" is not optimized for your Mac and needs to be updated. This app will not work with future versions of macOS and needs to be updated to improve compatibility. Contact the developer for more information.
Pop up you get whenever you run an app that’s not 64-bit on your Mac.

Despite the fact that the frequency of those messages is a bit exasperating, you need to pay attention to the issue they raise.

Apple is switching to an all 64-bit platform starting with macOS Catalina. All applications that are of the 32-bit kind will not be compatible with Apple’s operating system past the macOS Mojave (Version 10.14)

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.

AntivirusLittle TipsMalwareNetworkingPhishing

Microsoft SharePoint Under Attack – CVE-2019-0604

SharePoint is under attack as attackers have discovered and are exploiting vulnerability CVE-2019-0604. Find out more about the vulnerability in the linked security advisory by Microsoft below:

A remote code execution vulnerability exists in Microsoft SharePoint when the software fails to check the source markup of an application package. An attacker who successfully exploited the vulnerability could run arbitrary code in the context of the SharePoint application pool and the SharePoint server farm account.

– Microsoft SharePoint Remote Code Execution Vulnerability

According to an article by HelpNetSecurity, the attackers are able to install a web shell that then “allows them to achieve continuous access to the system and, potentially, to the internal network on which it resides.” The article also reports that, “According to the Canadian Centre for Cyber Security, researchers have identified compromised systems belonging to the academic, utility, heavy industry, manufacturing and technology sectors.”

What is the God Mode on Windows?
Little TipsWindows 10 TipsWindows PCWindows Server Tips

What Is This So Called God Mode on Windows and How Do I Access It?

Windows has a really cool tool you will be glad to discover if, like me, you like to have lots of power on you computer. Some people have called this feature the “God Mode,” but we will call it “Power Tools Mode,” because we fear God here.

The Power Tools Mode is easy to access. Just create an empty folder on your Desktop, Rename the folder to the following exact code with the brackets and dot and then press Enter.

PowerToolsMode.{ED7BA470-8E54-465E-825C-99712043E01C}

The folder icon should change to look like that of the Control Panel et voila!

Icon for the so-called God Mode on Windows

The new icon

This folder gives you access to tools like:

  • Shortcuts to several actions in the Action Center (including a shortcut to View the Reliability History discussed in one of our articles),
  • Several Administrative Tools,
  • Devices and Printers,
  • Several Accessibility features in the “Ease of Access Center.”

What is the God Mode on Windows?

Power Tools accessible via the so-called “God Mode” on Windows.