You’re trying to use a simple COUNTIF function, but you get an “#ERROR!” message instead. What does it mean and how can you fix it?
Google Sheets doesn’t have a compiler. But when you ask it to assign a value or calculation to a cell, it will check to make sure that the value or calculation is correct. If it isn’t, it will send you an error.
Unfortunately, there are a lot of errors out there. It may not be immediately obvious what each error means.
In this article, we’ll go over all the common formula parse errors in Google Sheets — and how to fix them.
Formula Parse Errors in Google Sheets
With some minor exceptions, errors in Google Sheets generally occur as silent values inserted into a given cell. So, if cell B4 has a #DIV/0! (divide by zero) error, cell B4 will display #DIV/0!
Whenever you see something that looks like this, you know it’s an error:
Errors start with a # (pound sign) and end with a ! (exclamation point). And, usually, that error has to be fixed. While you can leave it in your spreadsheet without harm (the rest of your spreadsheet will be unaffected), your calculations will likely be incorrect.
There is one notable exception: the “There was a problem” pop-up. If you create a syntax error that the computer can’t understand, it will pop up “There was a problem” to warn you and let you fix it.
It’s important to fix errors as you go in Google Sheets. If you don’t fix your errors, it’s possible that your sheet is giving you a result that’s incorrect. A budget with errors could just be a budget that’s not counting every number.
That being said, since every error is different, you need to understand the error code if you want to resolve them.
#DIV/0! – You Are Trying to Divide by Zero
Just as in a calculator, you can’t divide by zero in Google Sheets. But you probably already know that you shouldn’t enter in “1/0.” The “divide by zero” error most commonly pops up when you have a long list of calculations that you’re doing and one happens to be 0.
In the above situation, you didn’t intend to divide by zero — it happened accidentally because of the values that were fed into your formula. You could easily fix this like so:
Now you’ve used the IF operator to tell Google Sheets to divide only if the number is more than 0.
You should always consider the fact that a value might not be what you expect it to be when you develop a sheet. Validate your data properly if you don’t want your sheet to break when it’s given unexpected data. There will be a time that someone enters in a “0” in a slot that shouldn’t hold an “0.”
#ERROR! – Your Entry Doesn’t Make Sense
An #ERROR! will occur if you’ve entered in something that does make sense in Google’s syntax, but it doesn’t actually amount to anything. In the above example, nested parentheses are frequently used to capture additional arguments and functions within a cell. But the nested parentheses don’t include anything.
Though this isn’t strictly wrong in terms of syntax, it also doesn’t mean anything. So, Google returns a rather generic #ERROR! as a request for you to fix not the syntax, but the logic. If you see an #ERROR! message, it’s likely that what you typed in doesn’t exactly do what you think it does.
But how can you fix it? Note that as you type out a formula, Google Sheets will automatically start writing out that formula for you. You should be able to see which variables you need to pass to a given formula and how the formula will work (in brief).
Following along with the Google Sheets guide will be an easy and simple way to learn how to use more functions.
#N/A – Your Item Was Not Found
The #N/A error generally occurs when something isn’t found that was expected. It’s most common when you’re using the LOOKUP, VLOOKUP, and HLOOKUP functions. You can also find it in more niche functions like IMPORTXML.
In the above example, we’re using VLOOKUP to look up the number of Apples in a chart. But if we used “Kiwi” instead:
Now we get an #N/A error. An N/A error is a sign that we need to fix what we’re looking for. When it comes to a LOOKUP function, we could use “fuzzy” parameters (the best possible match) or we could simply validate the table first (make sure that something returns).
But #N/A doesn’t just occur when you’re using a LOOKUP function; that’s just when they’re the most likely. It’s so common that there’s a function called ISNA(), which can be used to validate your data and make sure that your information is used correctly.
#NAME? – You’re Incorrectly Applying a Label
#NAME? is a slightly more esoteric error condition. In Google, you can name a range; this makes it a “named range,” making it easier to reference. But you need to enclose the name within quotes (“Rainbow”). When you just use a word (Rainbow) it will pop up with #NAME?
If you want to name an importrange, you need to do it correctly. And make sure to validate the names that you do use. If you’ve never used a named range, you may want to look further into it — it can clean up and streamline your code.
#NULL! – You Are Using Microsoft Excel!
In fact, you shouldn’t see this error in your Google Sheet. #NULL! is a commonplace error in Microsoft Excel, when a value is returned that is empty though it is expected not to be. Google tends to be more forgiving than this, usually just treating a null value as a blank space or a zero, depending on need. You can even set things to “null” without getting a #NULL! error.
For the most part, it’s not a terrible idea to assume that if something works one way in Microsoft Excel, it’ll work the same way in Google Sheets. But that isn’t always true. #NULL! is an excellent example.
#NUM! – Your Number is Too Large to Display
In this situation, a given calculated field is just too large to be displayed. The number that we’re trying to calculate is above the feasible limits of any system, so Google Sheets isn’t going to waste time trying to calculate it (and, ultimately, locking up your system).
Instead, Google Sheets will warn you about numbers that are outside of the range that can be depicted. If this happens to you during intense calculations, then you know that either something has gone wrong, or you need to wildly reduce the amount of precision that you’re dealing with.
There may be times when you need a high level of precision or Very Large Numbers, but it’s unlikely that most people will ever stretch the capacity of Google Sheets’ cells. If you’re seeing the #NUM! error, it’s much more likely that you’ve accidentally typed something you didn’t need to.
#REF! – Your Reference No Longer Exists
Another simple error, the #REF! error refers to a reference that doesn’t or no longer exists. In the above example, we’re referring to a sheet (Sheet2) that doesn’t exist, so we get a #REF1 error.
But people don’t commonly refer to something that doesn’t exist altogether. Usually, this happens because a sheet gets deleted.
Luckily, in Google Sheets you can always review previous iterations of a file. You can even launch it as a copy or replace the current file altogether. If you accidentally delete a reference, you can pull up an older sheet and see where that reference came from.
#VALUE! – Your Item is Not the Expected Type
The #VALUE error occurs when something isn’t the expected type. This error is simple to fix. In the above, we are trying to add together two strings. We can’t’ do that! If we replaced them with numbers, the formula would work fine.
Of course, it doesn’t always look that simple. You can get a #VALUE error with more complicated formulas, which requires that you look at the assigned values to ensure that the right value is being passed.
Whenever you get a #VALUE! error, you should review the formula itself. You may be misusing the formula, which is what’s leading to the vague error.
“There was a problem” – You’ve Written Your Formula Incorrectly
This is the most common formula parse error in Google Sheets. It can also be one of the more frustrating errors to get. If you type something wrong (frequently, it’s just a typo), you’ll get a pop-up with “There was a problem.” This pop-up can be a little annoying because it’ll interrupt whatever you were doing. You’ll have to close the box and fix the syntax.
In the above screenshot, we just mistyped. We wanted to say “sum()”, but instead we ended up typing “sum()/”.
The “There was a problem” pop-up is distinct from #ERROR!. The #ERROR! appellation means that while the syntax appears to be correct, it doesn’t make sense to the compiler. You have technically typed something that is accurate, but it has no meaning.
Comparatively, a formula parse error means that your formula syntax does not make any sense. It will never run, even if the right values are fed into it. A formula parse error is fairly serious, but it’s also fairly trivial; it should be easy to tell what’s wrong.
Dealing With Errors using IFERROR()
The IFERROR() function is easy to use when you want to “clean up” errors. Wrap a function up with IFERROR(), and you can have the program take a different tactic entirely when errors are discovered, depending on what the discovered error is.
The syntax of IFERROR() is very simple:
So, it essentially operates as any IF command. If the cell has an error, it will print out the “value_if_error.” Otherwise, it will leave the cell alone. There are other ways to manage a cell, but IFERROR() is one of the simplest, cleanest, and most readable.
Using Google’s Built-In Error Documentation
At this point, you might have noticed that Google has a type of built-in error documentation. Every formula that Google Sheets supports is tied into a database within Google Sheets itself. As you start to type in formulas, you will see pop-ups guiding you through their completion.
Let’s revisit an error:
In the #ERROR! above, you might be tempted to believe that you’ve calculated a number too large. But this number isn’t too large at all. If it was, you’d be getting a #NUM! error, not an #ERROR! error.
Just hold your mouse over a cell with a red triangle in the upper right corner and you’ll see exactly what type of error you’ve received. In the above, you can see that it’s a “Formula Parse Error.” From there, you’ll hopefully notice the extraneous “_” that is holding the formula back.
Whenever you get an error, the first thing you should check is the red triangle. If you just hover over any errors you get, you’ll get more information about how to fix them. When in doubt, you can go to Google Sheets tutorial pages to learn more.
Reaching Out for Help
One of the major advantages of Google Sheets is that it has such a thriving community throughout the world. If you still can’t figure out how to fix your errors, you might not be alone. An online Google Sheets community (or even Google Sheets’ technical support) may be able to uncover problems that you have missed.
A primary benefit to Google Sheets is that you can add other people in as readers, commenters, and editors. If you’re struggling with your Google Sheet, consider sharing it with others who know the ropes. They’ll be able to get your issues fixed quickly through sheer experience.
Finding the Cause of Your Errors
Finding the cause of your error begins with figuring out what the error code means. If you get an #ERROR! message, you know that there’s something wrong with your logic. If you get a #REF! message, you know that you probably just deleted a reference. If you get a #NUM! message, you know that you definitely tried to do something with numbers far too large.
Google Sheets, just like other complex software platforms, can require a bit of trial and error.
There will always be errors. Understanding these errors, being able to track them down, and being able to fix them will all make it easier for you to make useful and attractive spreadsheets.