Formula error messages
USM Content
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.
Troubleshooting issues with formulas
USM Content
Users on an Enterprise plan can use AI to generate formulas or text.
Still need help?
Use the Formula Handbook template to find more support resources and view 100+ formulas, including a glossary of every function 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.
Frequently asked questions
Why is my formula displaying as text in the cell even though I typed it correctly?
These are three likely reasons why your formula is appearing as plain text in a cell:
- It’s missing the equal sign (=): Formulas must always start with =
- Dependencies are enabled in the sheet: When you've enabled dependencies on a sheet, the following dependency-enabled columns can't include formulas:
- Start
- Duration
- Finish
- Predecessors
- % Complete or % Allocation: These columns automatically generate values based on the dependency settings and overwrite formulas
- You created the formula from a report or a mobile device: Smartsheet doesn't currently support formulas created from reports or Android and iOS apps.
Why isn’t my formula referencing % Complete working?
Cells in sheets or reports use the decimal value, not the percentage format. This is true even for columns using percent-formatted values.
To compare percent values or reference percent columns, refer to the following table:
| Instead of this | Do this |
| =IF([% Complete]@row < 25%, "At Risk", " ") | =IF([%Complete]@row<.25,"AtRisk","") |
How can I fix a formula that isn't working when referencing the Start/End Date column?
When you've enabled dependencies on a sheet, the Start Date and End Date columns have an implicit time component. The time doesn't display in the column, but it can affect the results of formulas.
For example, you have a formula that subtracts TODAY() from the start date—which is today. You would generally expect a result of 0. However, you'd get a decimal value, as Smartsheet tries to perform the subtraction based on the implicit time stamp. To work around this issue, use the DATEONLY function.
To accompany the example, the formula =[Start Date]2 - TODAY() would change to =DATEONLY([Start Date]2)—TODAY().
In this case, Smartsheet only looks at the date, not the time, when calculating.
How can I fix the inaccurate results when computing the difference between the Created Date and Modified Date columns?
Use the DATEONLY function. Dates in system columns are display values, not actual values. System columns record UTC dates but display your local date/time based on the UTC date.
When you use DATEONLY, the date is extracted from the System column.
Here’s a sample formula: =NETWORKDAYS(DATEONLY(Created@row), TODAY()).
Why isn't my formula with TODAY() updating to today's date?
Opening reports and dashboards referencing a sheet with a formula using the TODAY function doesn't update the TODAY function to the current date. There must be an update to the underlying sheet itself.
To automatically update your underlying sheet without opening it, see Automatically update the TODAY function in formulas or use Bridge to Schedule daily sheet save.
Why are my numeric values not calculating correctly? For example, adding 1 + 12 produces the result 112 instead of 13.
This calculation error often occurs because one referenced value is considered a text value.
To correct this, check that neither of the referenced values has a character that would turn the numeric value into a text value. For example, if the value 12 had an (‘) apostrophe before it.
Also, check the formatting alignment of numeric values. Often, values considered text values appear left-aligned in the cell instead of the regular right-alignment for numeric values.
Numeric values appear left-aligned when they’re:
- Produced by formulas
- In the Primary column
However, these values are still numeric unless something in the formula or cell dictates otherwise.
Another way to fix this is using the VALUE() function, which converts text strings with numeric values into numbers.
Why is my SUMIFS formula returning a 0 when it should return a value?
If you’re evaluating the same range of data for multiple criteria, use the OR Function and @cell parameter to have the correct value returned.
Item | Quantity | Cost | Warehouse |
Shirt | 26 | 20.00 | A |
Pants | 24 | 50.00 | A |
Socks | 10 | 10.00 | A |
Shirt | 18 | 25.00 | B |
Pants | 16 | 75.00 | B |
Socks | 46 | 15.00 | B |
Use:
| Formula | Description | Result |
| =SUMIFS(Cost:Cost, Quantity:Quantity, >15, Warehouse:Warehouse, "A", Item:Item, OR(@cell = "Shirt", @cell = "Socks")) | Sums the Cost of anything with a Quantity over 15 from Warehouse A where the item is either Shirt or Socks | 20.00 |
Why is my COUNTIFS formula returning a 0 when it should return a value?
If you're evaluating the same range of data for multiple criteria, use the OR Function and @cell parameter to have the correct value returned.
Item | Quantity | Cost | Warehouse |
Shirt | 26 | 20.00 | A |
Pants | 24 | 50.00 | A |
Socks | 10 | 10.00 | A |
Shirt | 18 | 25.00 | B |
Pants | 16 | 75.00 | B |
Socks | 46 | 15.00 | B |
Use:
Formula | Description | Result |
=COUNTIFS(Quantity:Quantity, >15, Warehouse:Warehouse, "A", Item:Item, OR(@cell = "Shirt", @cell = "Socks")) | Counts anything with a Quantity over 15 from Warehouse A where the item is either Shirt or Socks | 1 |