Learning Track
This Help Article appears in the Level 1: Get Started learning track. Get the most out of this learning track by starting at the beginning.
This Help Article appears in the Level 1: Get Started learning track. Get the most out of this learning track by starting at the beginning.
The sheet Owner and Admins can create and edit formulas in locked and unlocked fields. The Owner, Admins, and Editors can create and edit formulas in unlocked fields.
Use formulas to calculate numeric values or automate aspects of your sheet. You can create formulas, for example, to sum a range of expenses, or to check a box when a specific value is entered in a field.
You can create a formula in the following field types:
To place a formula in a field:
Formulas can be placed in Contact List fields. Automatically assign people to tasks with a VLOOKUP formula, for example. Note that formulas can’t be placed in Contact List fields being used for resource management.
Formulas can include functions, such as SUM, AVG, or IF. Functions perform calculations with or manipulate data in sheets. For the complete list of functions available in Smartsheet, see the Smartsheet Functions List.
You can use the following operators in Smartsheet formulas.
Symbol | Description |
+ | Add |
- | Subtract |
* | Multiply |
/ | Divide |
^ | Exponent |
< | Less than |
> | Greater than |
>= | Greater than or equal to |
<= | Less than or equal to |
= | Equal to |
<> | Not equal to |
To edit an existing formula:
When you need a formula applied consistently and uniformly to an entire column, column formulas are the perfect solution. Build your formula in a cell, and quickly convert it to apply to every cell in the column. Regardless of how new rows get inserted into the sheet, the column formula will automatically apply. For more information, see Set Formulas for All Rows with Column Formulas.
If you're not sure which function to use, you can click the down arrow on the right of the Sum button in the toolbar and select a function.
The Sum button will attempt to provide you with a formula based on the field you have selected in the sheet and any hierarchical relationships with the selected field. For example, selecting the field of a parent row, then clicking the Sum icon will produce a =SUM(CHILDREN()) formula in the field.
After you create a formula, you can modify it at any time by double-clicking in the selected field or by pressing F2 (fn + F2 on a Mac).
Smartsheet treats percentages as values between 0 and 1. When you create formulas in columns formatted for percent (using the Percentage Format button in the toolbar), use decimal values. For example...
=0.5 + 0.4
...will return 90% in a column formatted for percentage...
=5 + 4
...will return 900% in a column formatted for percentage.
If you have a formula that you’d like to use in multiple fields without having to manually type the formula in each field, use the following methods to quickly copy the formula to other areas of your sheet:
Learn more about this functionality in the help articles on Drag-fill and Auto-filling Formulas and Formatting.
These fields can't contain formulas:
You can't create formulas in these features:
Formulas can't be edited from the following features in Smartsheet: