Applies to

Smartsheet
  • Pro
  • Business
  • Enterprise

FAQs: Using formulas

Formulas in Smartsheet save time by calculating data automatically, combining cell contents in a sheet, and helping you keep track of dates, among other things. 

Who can use this?

Plans:

  • Smartsheet
  • Pro
  • Business
  • Enterprise

Users on an Enterprise plan can use AI to generate formulas. Learn more.

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

Frequently asked questions

Can a formula reference a cell in a different sheet?

Yes. A formula can reference a single cell, range of cells, or full columns in another sheet with cross-sheet formulas

 

Does Smartsheet support a VLOOKUP function?

We do! However Smartsheet has an INDEX and MATCH combination that is more versatile. This combination reduces cross-sheet cell references by looking at columns individually instead of using one large range.

Take a look at our article on formula combinations for cross sheet references for an example.

You can still learn about Smartsheet's VLOOKUP function in this VLOOKUP article as well, or find examples of how other Smartsheet customers gather data using formulas in the Smartsheet online Community.

How can I get a formula to include rows that are newly added?

If a new row is inserted above, below, or between two rows that include the same formula, the new row will inherit that formula automatically.

If you're creating a formula to use all values within a column, exclude the row references and only use the column name in the included range (e.g. =SUM([Total Hours]:[Total Hours]). This ensures that any added rows will be automatically included in the calculation.

If you've set up a column formula, the formula will automatically apply to every new row that gets added to the column.

Can columns with a symbol column type be controlled by formulas?

Yes. The character added in the formula depends on the symbols you use. Refer to this list for what to add in the formula:

  • Star, flag, and checkbox symbols: For these symbols, use 1 for on or 0 for off
    • Example: = IF([Status]@row = "Complete",1,0)
  • Red, yellow, and green light symbols: Use the literal values Red, Yellow, or Green
    • Example: =IF([Status]@row = "At Risk", “Red”, "Green")
  • Priority symbols: Use High or Low
    • Example: =IF([Status]@row = "Overdue", "High", "Low")
  • Harvey Balls: Use Quarter, Half, Three Quarter, Full, or Empty
    • Example: =IF([% Complete]@row = 1, "Full", "Empty")

I typed my formula correctly, but it's displaying as text in the cell. What went wrong?

These are three likely reasons why it’s appearing as 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. If you type a formula in one of these columns, it’ll be displayed as plain text rather than performing a calculation.
  • You created the formula from a report or a mobile device: We don't currently support creating formulas from reports or from the Android and iOS apps. Any formula you enter from reports or from a mobile app will display as plain text rather than calculate or find values.

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, "At Risk", " ")

 

Can I create formulas in project sheet columns?

When dependencies are enabled, columns such as Start Date, End Date, Duration, and % Complete have inherent formulas to provide project functionality. Formulas aren't allowed in these columns, as they may be overwritten. 

To work around this behavior, you can either disable dependencies on the sheet or create new columns to perform the desired calculations.

My formula referencing the Start/End Date column isn't working. How can I fix it?

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 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. What you'd actually get is 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 go along with 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 performing the calculation.
 

I’m seeing inaccurate results when I compute the difference between the Created Date and Modified Date columns. How can I fix it?

Use the DATEONLY function. Dates in System columns are a display value and not the actual value. System columns record UTC dates, but they display your local date/time based on the UTC date. You may see results you don’t expect if you don’t use DATEONLY function. 

When you use DATEONLY, it extracts the date 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 that reference 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.

 
You can see a list of actions that will update the formula in the TODAY Function article.

To automatically update your underlying sheet without opening it, take a look at the Automatically update the TODAY function in formulas article or use Bridge to Schedule Daily Sheet Save.
 

What happens to my formulas if I use the "Move to Another Sheet" or "Copy to Another Sheet" commands to move or copy a row containing a formula to another sheet?

Formulas won't be preserved when you use these commands to move or copy rows. You can, however, use keyboard shortcuts to copy and paste formulas.

Why are my numeric values not calculating correctly? For example, when adding 1 + 12 it’s producing the result 112, instead of 13.

Often, this calculation error occurs because one of the referenced values is being considered as a text value. 

To correct this error, check that neither of the values being referenced has a character that would turn the numeric value into a text value. For example, if the value 12 had an (‘) apostrophe before it, this undesired result occurs.

A trick to catch this is to check the numeric values formatting alignment. Often, values being considered as a text value appear left aligned in the cell, instead of the normal 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 values. Unless something in the formula or cell dictates otherwise. 

Another option to correct this error is to use the VALUE() function. The VALUE() function converts text strings containing numeric values to numbers.

I’m using a SUMIFS formula that should be returning a value, but it is returning a 0.

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.

ItemQuantityCostWarehouse
Shirt2620.00A
Pants2450.00A
Socks1010.00A
Shirt1825.00B
Pants1675.00B
Socks4615.00B

 

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 Socks20.00

I’m using a COUNTIFS formula that should be returning a value, but it is returning a 0.

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.

ItemQuantityCostWarehouse
Shirt2620.00A
Pants2450.00A
Socks1010.00A
Shirt1825.00B
Pants1675.00B
Socks4615.00B

 

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

Will formulas that produce a text value return the value as a text value in a contact cell?

Yes. If the cells being referenced by the formula are text values and don’t create an email address, they’ll return as only a text value. 

 

What functions work in a contact column?

Functions that can return a contact value work in contact columns. Here are some sample functions you can use:

  • Equals Contact cell: =[Assigned To]1
  • VLOOKUP: =VLOOKUP([Assigned To]1,{Departments},2,false)
  • PARENT: =PARENT()
  • INDEX(MATCH()): =INDEX({Departments},MATCH([Assigned To]1,{Names},0))
  • IF: =IF(Urgency1="High",[Urgent on call]1,[Assigned To]1)

Below are some specific examples of these formulas in action.

Sample sheet information:

Row Project nameAssigned toDate% CompleteStatus
1Project - ES Brown03/25/2030%Red
2Outreachdon.t@domain.com10/18/2060%Yellow
3Smartsheet - PMAndy Davis01/06/20100%Green
4Collection - INTJohnny Apple, S Brown02/12/2020%Red

Given the table above, here are some examples of using Contact Formulas in a sheet:

FormulaDescriptionResult
=INDEX([Project Name]:[Project Name], MATCH("Andy Davis", [Assigned To]:[Assigned To]))Returns the value in the Project Name column for the row that contains the value Andy Davis in the Assigned to columnSmartsheet - PM
=COUNTIF([Assigned To]:[Assigned To], HAS(@cell, "S Brown"))Counts the number of rows where the value in the Assigned to column has the value S Brown2
=VLOOKUP("don.t@domain.com", [Assigned To]:[Date], 2, false)Returns the date. If the Assigned to column has the email don.t@domain.com look up and produce the value at the same row in the Date column10/18/20

How do I create a new contact using a formula?

Currently, we don’t have a direct method to create new contacts with formulas. You can create  new contacts using multiple columns if the email addresses of the org follow a particular pattern. For example, first.last@domain.com.


To do this, you can have a column for first.last and another for @domain.com. Using the JOIN function can bring these two fields together into the same contact cell, and create the desired contact.

Can I use formulas that output data other than contacts or text values in a contact column?

Formulas that output data types other than contacts or text (e.g. dates, numbers, booleans, etc.) aren’t supported and will return an error code: #CONTACT EXPECTED. This is similar to how DATE columns work today. Formulas calculate to #DATE EXPECTED, if they output anything other than dates or text. Checkbox columns have a similar error: #BOOLEAN EXPECTED.

 

Was this article helpful?
YesNo