## Applies to

- Pro
- Business
- Enterprise

# Frequently asked questions about 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.

This article provides answers to common questions (FAQs) about formulas.

#### 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.

## 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! 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 this | Do 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.

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 |
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 |
1 |

#### 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 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 |
Smartsheet - PM |

=COUNTIF([Assigned To]:[Assigned To], HAS(@cell, "S Brown")) |
Counts the number of rows where the value in the |
2 |

=VLOOKUP("don.t@domain.com", [Assigned To]:[Date], 2, false) |
Returns the date. If the |
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.

#### 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.