Frequently asked questions about using formulas

Applies to

Smartsheet
  • Pro
  • Business
  • Enterprise

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. 

To help you get up to speed on using formulas, this article is a compilation of answers to common questions (FAQs) that customers often have as they’re ramping up.

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

Answers to Questions About Formulas in Smartsheet

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! You can find out more about this in the VLOOKUP Function article.

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 this Do this
=IF([% Complete]5 < 25%, “At Risk”, “ “) =IF([% Complete]5 < .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()).
 

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. 
 

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.

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

 

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

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.

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

 

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

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 name Assigned to Date % Complete Status
1 Project - E S Brown 03/25/20 30% Red
2 Outreach don.t@domain.com 10/18/20 60% Yellow
3 Smartsheet - PM Andy Davis 01/06/20 100% Green
4 Collection - INT Johnny Apple, S Brown 02/12/20 20% Red

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

Formula

Description

Result

=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 column

Smartsheet - 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 Brown

2

=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 column

10/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.

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. 

 

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.