Troubleshooting issues with formulas

Learn how to troubleshoot some of the most common issues with formulas.

Who can use this?

Plans:

  • Smartsheet
  • Pro
  • Business
  • Enterprise

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.

Ask the 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 it’s 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 dependencies are enabled on a sheet, formulas aren't allowed in these dependency-enabled columns
    • 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: Formulas created from reports or Android and iOS apps aren't currently supported 

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 table below.

Instead of thisDo 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 dependencies are enabled on a sheet, the Start Date and End Date columns have an implicit time component. The time isn't displayed 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 be revised 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 will not 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 and 
  • In the Primary column.

However, these values will still be recognized as 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:

FormulaDescriptionResult
=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 are 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