Applies to
- 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 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
Can a formula reference a cell in a different sheet?
Yes. Cross-sheet formulas can reference a single cell, a range of cells, or full columns in another sheet.
Does Smartsheet support a VLOOKUP function?
We do. However, Smartsheet has a more versatile INDEX and MATCH combination. This combination reduces cross-sheet cell references by looking at columns individually instead of using one large range.
You can also learn about Smartsheet's VLOOKUP function 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 that includes the same formula is added, 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, it will automatically apply to every new row added to the column.
Can formulas control columns with a symbol column type?
Yes. The character added in the formula relies on the symbols you use. Refer to this list for what to add to 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")
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 deactivate dependencies on the sheet or create new columns to perform the desired calculations.
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.
Will formulas that produce a text value return the value as a text value in a contact cell?
Yes. If the cells 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], 0)) | 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?
There isn’t a direct method to create new contacts with formulas. However, you can create new contacts using multiple columns if the org's email addresses 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, bring these two fields 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.