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 each common formula parse error in Google Sheets — and hoe to fix them
Table of Contents
What Is a Formula Parse Error?
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:
#DIV/0!
#ERROR!
#NUM!
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. Let’s take a look at how to fix a formula parse error in Google Sheets.
#DIV/0! – You Are Trying to Divide by Zero
Just as in a calculator, you can’t divide by zero in Google Sheets. #DIV/0! is a Google Sheets formula parse error to warn of this mistake. But you probably already know that you shouldn’t enter in “1/0.”
How to Fix the Error
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.
For instance:
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.
How to Fix the Error
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 for the particular skill you’re learning will be an easy and simple way to learn how to use more functions. It’s often caused by missing parts in the function such as quotation marks, brackets, or commas.
#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.
How to Fix the Error
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. In other words, the best way to fix an #N/A error is to make sure the data in your table is valid.
#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.
How to Fix the Error
You must 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 needs. 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.
How to Fix the Error
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 mean to. Check your inputs.
To change the accuracy requirements, you can use the move decimal place shortcuts in the toolbar.
#REF! – Your Reference No Longer Exists
The #REF! Google Sheets 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 #REF! error.
But people don’t commonly refer to something that doesn’t exist altogether. Usually, this happens because a sheet gets deleted.
How to Fix the Error
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. You may also be able to press Ctrl + Z if deleting the reference in the same sheet was your last action.
#REF! will often show up if the cell your result is in is also in the specified range so you should remove it if that’s the case. For example, if your total is in cell B6 and your formula is =SUM(B1:B6) change it to =SUM(B1:B5).
#VALUE! – Your Item is Not the Expected Type
The #VALUE error in Google Sheets 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.
How to Fix the Error
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.
Other Strategies for Dealing With a Formula Parse Error in Google Sheets
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.
Functions to Help Deal With Formula Parse Errors in Google Sheets
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:
IFERROR(value,[value_if_error])
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.
=ERROR.TYPE()
Gives the type of error through a numerical value:
- 1 = #NULL!
- 2 = #DIV/0!
- 3 = #VALUE!
- 4 = #REF!
- 5 = #NAME?
- 6 = #NUM!
- 7 = #N/A
- 8 = All other errors
=ISNA()
Provides a true value of there is an #N/A error in the specified range.
=ISERR()
Provides a true value if there is any other type of error in the specified range.
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.
Frequently Asked Questions
How Do I Fix a Formula Parse Error in Google Sheets?
First identify what the error code means. Mostly, there’s a missing reference or your formula is incorrect. Double check your formula against examples online.
What Is Parse Error in Google Spreadsheets? / What Does Formula Parse Error Mean?
Let’s take a quick look at formula to parse error meaning. A parse error in Google Sheets is a blanket term for a formula error. It accounts for the most possible problems that can occur when typing a formula. For example, #REF! means the reference does not exist in Google Sheets, so you should check the range in the formula is referencing an appropriate part of the spreadsheet.
How Do You Refresh Formulas in Google Sheets?
By default, formulas refresh when a change is made. But, you can also set them to refresh based on time by navigating to File > Spreadsheet settings > Recalculations and picking the appropriate option.
Why Is My Sum Formula Returning 0 Google Sheets?
The most likely explanation is that your values aren’t formatted as numbers. Make sure they are by using =ISNUMBER(cell range) in an empty cell, it will return FALSE if there are values that aren’t numbers in the range. Another possible reason is that you have an * in your formula and a null value cell. Anything multiplied by zero is zero.
Finding the Cause of Your Errors
Finding the cause of your formula parse error in Google Sheets 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.
Related: