
Smartsheet Formula Errors: Every Error Code and the Fix That Works
When a Smartsheet formula fails, the cell shows a code such as #UNPARSEABLE, #INVALID DATA TYPE, or #NO MATCH. Each one points to a different cause, so read the code before you start editing. Smartsheet’s formula error messages article (checked November 18, 2026) lists 21 codes. See the table below.
I build Smartsheet solutions for clients (SSPM is a Smartsheet Premier Partner). In practice, five codes cause most tickets: #UNPARSEABLE, #INVALID DATA TYPE, #NO MATCH, #BLOCKED, and #CIRCULAR REFERENCE.
What does #UNPARSEABLE mean in Smartsheet?
#UNPARSEABLE means Smartsheet cannot read the formula. The usual causes are a misspelled function or column name, or the wrong capitalization in a column name. An incomplete or unsupported operator (such as =< instead of <=) can also cause it, as can single quotes around text. Smartsheet needs double quotes for text. That list is in the formula error messages article (checked November 18, 2026).
Fix it by checking every column name exactly, counting parentheses, and changing ‘text’ to “text”. If the formula sits in the wrong column type, you may get a different code. A Contact List column may show #CONTACT EXPECTED. A function that gets text instead of a number may show #INVALID DATA TYPE.
The five you will see most

#UNPARSEABLE. Smartsheet cannot read the formula. Causes and the fix are in the section above.
#INVALID DATA TYPE. An argument has the wrong type, such as =INT("Hello"), or a number stored as text. Fix: point the argument at a cell of the right type, or convert with VALUE().
#NO MATCH. VLOOKUP, INDEX/MATCH, or MATCH found nothing. Fix: check that the lookup value really exists (watch for trailing spaces), that the range is right, and that the match type suits your data. INDEX/MATCH with match type 0 is the safest default.
#BLOCKED. Your formula is fine; a cell it references has an error. Fix the upstream cell and this clears on its own.
#CIRCULAR REFERENCE. The formula refers to itself, directly or through a chain. Fix: trace the references and break the loop, often by moving a summary formula to a different row or a sheet summary field.
Every code and its fix
| Code | What it means | Fix |
|---|---|---|
| #BLOCKED | A referenced cell has an error | Fix the upstream error |
| #BOOLEAN EXPECTED | Checkbox column got a non-true/false result | Return true/false or move the formula |
| #CALCULATING | Formula engine still processing (heavy sheet or chained references) | Wait; reduce chained formulas if it persists |
| #CIRCULAR REFERENCE | Formula references itself | Break the loop |
| #CONTACT EXPECTED | Contact column got a non-contact, non-text value | Return text or a contact |
| #DATE EXPECTED | Date column got a non-date value | Return a date or change column type |
| #DIVIDE BY ZERO | Division by 0 or blank | Wrap in IFERROR or test the divisor |
| #IMBALANCED | Unmatched parentheses | Count opening and closing brackets |
| #INCORRECT ARGUMENT SET | Wrong number of arguments or mismatched range sizes | Check the function syntax |
| #INVALID COLUMN VALUE | Output type does not match the column | Move the formula, change the column, or convert output |
| #INVALID DATA TYPE | Argument of the wrong type | Reference the correct type |
| #INVALID OPERATION | Unsupported or mistyped operator, such as =< | Use the correct operator (<=) |
| #INVALID REF | Cross-sheet reference name in curly braces does not exist | Fix the name or create the reference |
| #INVALID VALUE | Number outside the range an argument accepts | Keep number arguments in range |
| #NESTED CRITERIA | Criteria nested inside SUMIF or COUNTIF | Use SUMIFS or COUNTIFS |
| #NO MATCH | Lookup found nothing | Fix lookup value, range, or match type |
| #OVERFLOW | Value beyond the supported numeric range (or over 1,000,000 WORKDAY days) | Fix the cell references feeding it |
| #PERMISSION ERROR | Nobody has access to both sheets in a cross-sheet reference | One person needs Editor on this sheet and Viewer on the source |
| #REF | Referenced cell, row, or column was deleted | Rebuild the reference |
| #UNPARSEABLE | Formula cannot be read | Fix spelling, case, quotes |
| #UNSUPPORTED CROSS-SHEET FORMULA | CHILDREN, PARENT, or ANCESTORS used with a cross-sheet reference | Reference the cells directly instead |
Problems that do not show a code
- Formula shows as text. The column may be set to a type that does not accept formulas, or the formula is missing the leading
=. - 1 + 12 = 112. One value is text. Wrap it in VALUE() or fix the source column.
- Column formula will not apply. Some column types and system columns do not accept column formulas.

Habits that prevent errors
- Use IFERROR on lookups shown to stakeholders, but not while you are still debugging.
- Name cross-sheet references clearly (“Budget Lookup”) so others know what they point to.
- Keep column names stable. Renaming updates formulas, but deleting a column produces #REF.
- Keep heavy cross-sheet formulas off the main working sheet.
For building formulas from scratch, see our Smartsheet formulas guide. If formulas feed dashboards, the Smartsheet dashboards guide covers how to keep widgets stable.
How I debug a broken formula in five minutes
- Read the code in the cell. Hover over it. Each code points to a different cause (see the table above).
- Click into the formula and look at the colors. Smartsheet highlights each referenced cell or range. If a range is not highlighted, the name is wrong.
- Cut it down. Copy the formula to a spare cell. Remove the outer parts (like IFERROR or IF) until the small piece works. Then add parts back one at a time.
- Check the column types. Open Edit Column Properties on every column the formula reads or writes. Most #INVALID errors are type problems.
- Check one row by hand. If the formula says 0 and you expect 3, count it yourself on that row. You often spot a blank or a typo.
Real examples and fixes
#UNPARSEABLE from a curly quote
=IF(Status1 = “Done”, 1, 0) fails because the quotes are curly. This happens when you paste from Word or email. Retype the quotes in Smartsheet: =IF(Status1 = "Done", 1, 0).
#UNPARSEABLE from a column name with spaces
=SUM(Hours Worked:Hours Worked) fails. Columns with spaces or numbers need square brackets: =SUM([Hours Worked]:[Hours Worked]).
#NO MATCH from a trailing space
The lookup value is “ACME ” with a space at the end. The table has “ACME”. Clean it with TRIM, or fix the source data. INDEX/MATCH with a 0 match type is safer than VLOOKUP. It does not break when someone adds a column in the middle.
#INVALID COLUMN VALUE in a date column
A formula that returns text, like ="TBD", sits in a Date column. Change the column to Text/Number, or return a date.
#CIRCULAR REFERENCE in a parent row
A parent row sums its children, and one child refers to the parent total. Move the total to a sheet summary field instead.
Cross-sheet formulas: the extra traps
Cross-sheet formulas use a named reference in curly braces, like {Budget Range}. Things to know:
- Each reference needs a clear name. “Sheet1 Range 3” helps nobody. Name it after what it holds.
- Permissions decide if it works. At least one person must have Editor rights on your sheet and Viewer rights on the source sheet. If that person leaves the company, the formula can fail with #PERMISSION ERROR.
- Limits apply. A sheet can only hold so many cross-sheet references and cells. Heavy lookups across big sheets slow everything down. If a sheet shows #CALCULATING for a long time, move lookups to a helper sheet.
- CHILDREN, PARENT and ANCESTORS do not work across sheets. Use a direct range instead.
When NOT to use IFERROR
IFERROR hides errors by showing a blank or a fallback value. That is fine on a report for executives. It is a bad idea while you are building, because it hides the real problem. I also avoid it on money columns. A hidden error on a budget sheet means a wrong total nobody notices. Fix the cause, then add IFERROR only where people need a clean view.
Habits that prevent most errors
- Build formulas in a test copy of the sheet first.
- Lock column types before you share the sheet.
- Use dropdown columns for statuses, so “Done”, “done” and “Done ” can’t all exist.
- Keep a short “Formula notes” row or doc explaining what each column formula does.
Common questions
What does #UNPARSEABLE mean in Smartsheet?
Smartsheet cannot read the formula. Check spelling and capitalization of every function and column name, balanced parentheses, and double quotes around text.
How do I fix #NO MATCH in Smartsheet?
Check that the lookup value exists in the range (watch for extra spaces), that the range covers the right columns, and that the match type is correct. INDEX/MATCH with match type 0 is safest.
Why does my Smartsheet formula say #BLOCKED?
A cell your formula references contains an error. Fix that cell and #BLOCKED clears automatically.
Why does Smartsheet show my formula as text?
The formula may be missing the leading equals sign, or the column type does not support that formula’s output.
Why does my Smartsheet formula work in one row but not another?
Usually the data in that row is different: a blank cell, text instead of a number, or a stray space. Check the inputs on the failing row.
How do I fix #PERMISSION ERROR in Smartsheet?
Make sure at least one person has Editor access on the sheet with the formula and Viewer access on the source sheet. Then rebuild the reference if needed.
Error codes checked against Smartsheet’s Learning Center on November 18, 2026.
