Formula error messages

Keep the following in mind

• If you're on an Enterprise plan, you can use AI to generate or troubleshoot formulas. Learn more.
• You can also ask Smart Assist to help you understand or fix a formula error. Open Smart Assist from the right rail and describe the error you're seeing. Learn more. 
• Editors with sharing permissions can create and edit formulas in unlocked cells on a sheet.

#BLOCKED

Cause

The calculation is blocked because at least one cell referenced by the formula contains an error.

Resolution

Find the cell referenced by this formula that contains the error. Once you fix that underlying error, the #BLOCKED error resolves as well. The error message on that cell gives you a more specific description of the problem.

#BOOLEAN EXPECTED

Cause

The formula is in a checkbox column type (Checkbox, Flag, or Star) that expects a boolean value (true/false, typically represented as 0 or 1), but the formula's result is incompatible with the boolean field. This often occurs when a formula in a checkbox column returns a number greater than 1 or a date value.

Resolution

Adjust the formula to return a boolean value (0 or 1, true or false) or place the formula in another column type. If you need to display the non-boolean result in the checkbox column, you can convert a formula's result to a text value by adding an empty string to the end of the formula. For example: =TODAY() + "" converts today's date into a text string, which a checkbox column can display.

#CALCULATING

Causes

The formula engine is still processing. This typically happens due to:

• Network conditions or heavy browser usage
• A complex formula referencing cells that, at the same time, reference other cells via formulas or cell links

Resolution

This error usually resolves on its own once the calculation finishes. If it doesn't, a few things can affect sheet performance:

• Browser speed. Smartsheet performs best in Google Chrome. If you're troubleshooting slowness, try switching browsers.
• Advanced functionality. Widespread use of formulas, cell links, and conditional formatting can slow load times. Try disabling conditional formatting rules to see if that helps, and remove any formula columns or cell links you no longer need.
• Sheet size. Performance can decrease as sheets grow. When it makes sense, move rows to an archival sheet.

If you're using cross-sheet references, confirm the sheet isn't approaching the 100,000 cell reference limit.

#CIRCULAR REFERENCE

Cause

The formula references itself. The circular reference may be direct, where the reference is in the formula text itself, or indirect, where this formula references a cell, which then references back to this cell.

Resolution

Track down which reference is circular. Indirect references can be many levels deep. One approach: copy the formula and remove cell references one at a time until the error disappears. That process of elimination points you to the circular reference.

#CONTACT EXPECTED

Cause

A formula in a Contact List field has returned a datatype other than text or contacts.

Resolution

Formulas in Contact List fields may only return text or contact datatypes. Adjust the formula to return one of those datatypes or move the formula into another field type (Text/Number, Date, Dropdown List, Checkbox, or Symbol).

#DATE EXPECTED

Cause

The formula is in a specific column type (Date, Number, Symbol), but the formula’s result is a different type.

Resolution

Either move the formula to a different column or convert the result to the appropriate type. Add an empty string to convert formula results to text values. For example =TODAY() + "" allows you to enter today's date into a text column.

#DIVIDE BY ZERO

Cause

Your formula attempted to divide a value by zero (for example, =4/0).

Resolution

Wrap your formula in IFERROR to return a fallback value when the divisor is zero. 

For example: =IFERROR(100/Value4, "")

This returns a blank cell instead of an error when Value4 is zero.

#IMBALANCED

Cause

The parentheses in the formula don't match. The number of opening parentheses doesn't equal the number of closing parentheses.

Resolution

Review the formula for missing or extra parentheses and add or remove them as needed.

#INCORRECT ARGUMENT SET

Cause

This error appears in the following situations:

• For functions that take two ranges, the range sizes don't match.
• The function is missing a required argument.
• The function has an extra argument it doesn't expect.

Resolution

Correct the range size or arguments by adding or removing arguments in the formula.

#INVALID COLUMN VALUE

Cause

The formula contains or references a data type that doesn't match the column's type. For example, the following MAX formula is placed in a Text/Number column and references other values in the Date column.

=MAX([Due Date]1, [Due Date]52)

If the column containing the formula isn't a Date data type, it can't return the latest date in the range. 

Learn more about the MAX Function.

Resolution

Make one of the following adjustments to your sheet:

• Move the formula to a column whose type matches the type of value the formula returns.
• Right-click the column header and select Edit Column Properties. Change the column to a type that matches the formula's output.
• Add IFERROR to return a fallback value. For example: =IFERROR(MAX([Due Date]1, [Due Date]5), "Not a number!")
• Wrap the formula in a function that converts it to the expected type. For example: =WEEKNUMBER(MAX([Due Date]1, [Due Date]5)) placed in a Text/Number column returns the week number corresponding to the highest date. 

Learn more about WEEKNUMBER.

#INVALID DATA TYPE

Cause

The formula contains or references an incompatible data type, such as =INT("Hello").

Resolution

Make sure the formula references the correct data type.

#INVALID REF 

Cause

A reference name to another sheet (in curly braces) doesn't match an existing cross-sheet reference. 

Resolution

Update the name within the curly braces to match an existing cross-sheet reference, or create a new reference to the other sheet.

Learn more about referencing data from other sheets.

#INVALID OPERATION

Cause

An operator in the formula isn't supported in Smartsheet or is mistyped. For example, this IF statement:

=IF(Quantity1 =< 25, "Low Stock")

...uses the less-than-or-equal-to operator in the wrong order.

The correct operator is <=, making the formula:

=IF(Quantity1 <= 25, "Low Stock")

Resolution

Check all operators to confirm they're correctly typed and supported in Smartsheet. The most common cause is typing => or =< instead of >= or <=. 

See Create and edit formulas for a full list of supported operators.

#INVALID VALUE

Cause

The formula contains a number outside the range the function's argument expects. For example, this FIND formula:

=FIND("H", "Hello", 100)

...sets the starting position at 100 characters, but the string "Hello" is only five characters long. Only positions 1–5 are valid.

Resolution

Make sure that all number arguments are within the range that the function expects.

#NESTED CRITERIA

Cause

You’ve nested criteria in a SUMIF or COUNTIF function.

Resolution

Use SUMIFS or COUNTIFS instead. These functions support multiple criteria without nesting.

#NO MATCH

Causes

A VLOOKUP or MATCH function didn't find a result because:

• The value you're looking for isn't in the selected range
• The match type doesn't align with how the data is sorted

Resolution

• For VLOOKUP: Adjust the match_type or lookup_table of the VLOOKUP function such that it finds a result.
• For MATCH: Adjust the range or search_type of the MATCH function so the function finds a result.

#OVERFLOW

Cause

The formula encountered or computed a value that exceeds the supported numeric range. For numeric values, the range is –9,007,199,254,740,992 through 9,007,199,254,740,992. For WORKDAY calculations, the maximum number of days is 1,000,000.

Resolution

In most cases, inaccurate or incomplete cell references cause this error. Review the formula and correct the references.

#PERMISSION ERROR

Cause

The formula references data from another sheet (in curly braces), but no one has the required permissions to both sheets. 

Resolution

Make sure at least one person has Editor-level permissions or higher on the sheet containing the formula, and Viewer-level permissions or higher on the source sheet containing the referenced data.

#REF

Cause

The formula references a nonexistent cell, possibly due to a row/column deletion.

Resolution

Update your formulas manually to point to valid cells.

#UNPARSEABLE

Cause

The formula has a problem that prevents Smartsheet from reading and interpreting it. Common causes include:

• Misspelled function or column names
• Using an incomplete or unsupported operator
• Using the wrong case for a column name
• Using single quotes instead of double quotes around text strings

Resolution

Check that all column names are spelled correctly, operators are valid, and any text strings in the formula use double quotes (" ").

If you used AI to generate this formula, try rephrasing your prompt to be more specific about the expected output, then select Regenerate. Learn more about using AI to generate formulas.

#UNSUPPORTED CROSS-SHEET FORMULA

Cause

The CHILDREN, PARENT, or ANCESTORS function is referencing data from another sheet (in curly braces), which isn't supported.

Resolution

Remove the cross-sheet reference from that function. You can create a reference to another sheet and manually select the desired cells instead.
 

Still need help?

Use the Formula Handbook template to find more support resources and view 100+ formulas, including a glossary of every function that you can practice working with in real time, and examples of commonly used and advanced formulas.

Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.

Ask the Community