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 enters a cell.
NOTE: You must be an Editor, Admin, or the sheet Owner in order to create and edit formulas in a sheet. Editors can only create and edit formulas in unlocked cells. Details about locked cells can be found in our Locking and Unlocking Columns and Rows article.
In this Article:
- Create a Formula
- Cell References in a Formula
- Summary of Reference Types
- Drag-Fill and Auto-Fill to Save Time
- Video Demo
Work with Examples Right in Smartsheet
To see detailed examples—including syntax and parameter requirements—and to practice using formulas, try the Smartsheet Formula Examples template.
Create a Formula
You can create a formula in the following column types:
- Text/Number columns not being used for Dependencies or Resource Management. Check out our Using Dependencies and Resource Management Overview articles for more information.
- Dropdown columns
- Symbol columns
To place a formula in a cell:
- Select the desired cell.
- Type an equal sign in the cell, and then type the calculation.
- After you’ve typed in your formula, press [Enter].
The result is displayed in the cell containing the formula.
Formula Operators
You can use the following operators in Smartsheet formulas.
| Symbol | Description |
| + | Add |
| - | Subtract |
| * | Multiply |
| / | Divide |
| ^ | Exponent |
| < | Lesser than |
| > | Greater than |
| >= | Greater than or equal to |
| <= | Less than or equal to |
| = | Equal to |
| <> | Not equal to |
Use the Sum Tool
If you're not sure which function to use, you can click the down arrow on the right of the Sum button 
in the left toolbar and select a function.
The Sum button will attempt to provide you with a formula based on the cell you have selected in the sheet and any hierarchical relationships with the selected cell. For example, selecting the cell of a parent row, then clicking the Sum icon will produce a =SUM(CHILDREN()) formula in the cell.
After you create a formula, you can modify it at any time by double-clicking in the selected cell or by pressing F2 ([fn] + F2 on a Mac).
Edit an Existing Formula
To edit an existing formula:
- Double-click the cell containing the formula to open it for editing (or press [F2]).
- Make your desired formula changes, and then press [Enter].
TIP: If you change your mind about editing the formula, press [Esc] to exit edit mode and revert to the pre-edited formula.
Cell References in a Formula
To include values from other cells on the sheet in a formula, use cell references.
Reference Individual Cells
To reference a cell in a formula, type the column name followed by the row number.
For example, the formula in the Inventory Value column of the following inventory management sheet will multiply the value from row 1 of the Price column to the value in row 1 of the Stock column:
The formula returns $2,994.00, the total worth of that item:
Reference Column Names that Have Spaces or End in Numbers
If a column name contains spaces or contains any special characters or numbers, you must enclose it in brackets to avoid ambiguity:
=[Annual Budget]1 + [Annual Budget]2
=[Q1]1 + [Q2]1
=[Risk/Issue]5 + [Contingency/Mitigation]5
Create an Absolute Reference
You may have situations, such as when referencing a table with the LOOKUP function, where you need to prevent Smartsheet from automatically updating cell references when a formula is moved or copied. Create an absolute cell reference. (You can find details on the LOOKUP function in the Function Reference article.)
To create an absolute reference, type a $ (dollar sign) in front of the column name or row number in the cell reference of your formula. For example, if you move or copy the following formula, the column names and row numbers will not change for the cell references:
=$[Column A]$1 * $[Column B]$1
The following formula has absolute references to only the row numbers. If you move or copy the formula, the column references will change respectively based on the formula’s new location:
=[Column A]$1 * [Column B]$1
The following formula will maintain absolute references to the columns. If you move or copy the formula, the row numbers will change respectively based on the formula’s new location:
=$[Column A]1 * $[Column B]1
Reference a Range of Cells in the Same Column
To reference a range of cells, type a : (colon) between the two cell references.
For example, the formula at the bottom of the Inventory Value column of the following inventory management sheet will sum the values from row 1 to row 6 in the same column:
The formula returns $40,763.75, the total of all inventory values:
Reference a Whole Column
You can reference an entire column in a formula, including all populated cells in the column. For example, the following formula sums all values in the Annual Budget column and will remain updated as new rows are added to or removed from the column:
=SUM([Annual Budget]:[Annual Budget])
NOTE: If a formula is placed in the column it's referencing, it won't reference the cell that the formula is in. For example, if you were to place the example formula above in a column named Annual Budget, the SUM formula would sum all cells except for the cell containing the formula.
Reference a Range Across Multiple Columns
To reference a range of values in a row across multiple columns, reference the first and last column in the row.
For example, the formula in the Total Stock column of the following inventory management sheet will sum the values from the Stock A, Stock B, and Stock C columns on row 1:
The formula returns 998, the total stock from the three locations:
Summary of Reference Types
Here’s a cheat sheet you can use as you build formulas of your own.
| To refer to this | Format it this way | Examples |
| Individual cell | Column name, row number. | =Budget1 |
| Column name contains a space or ends in a number | Enclose the column name in brackets. | =[Column A]1 =DAY([Q1]1) |
| Absolute reference (always refers to that specific cell, row, or column) | Type a $ symbol in front of the column name, row number or both. | =$[Column A]$1 =[Column B]$1 =$[Column C]1 |
| Multiple, discontinuous cells | Use a comma between cell references. | =SUM(Budget1, Expenses4, [Projected Earnings]20) |
| A range of cells in the same column | Reference the first cell in the range, then the last cell, separated by a : (colon). | =SUM(Budget1:Budget12) |
| An entire column (including any newly added cells) | The column name separated by a : (colon). | =SUM(Budget:Budget) |
| A range of cells across multiple columns | Reference the upper-rightmost cell, then the lower-leftmost cell, seperated by a : (colon). | =SUM(January1:March5) |
Reference Children, Parents, and Ancestors with Hierarchy Functions
Hierarchy functions allow you to include cells in other functions based on their indent level in a sheet. You can place a hierarchy function inside of another function, for example, to automatically reference all indented child cells underneath a parent, even as new child rows are added to the parent row.
See Hierarchy: Indenting & Outdenting Rows for more information on creating parent and child row relationships.
Check out each function's respective Help Center article for details on their usage:
| CHILDREN Function | Reference all child cells underneath a parent. |
| PARENT Function | Reference the direct parent cell of a child cell. |
| ANCESTORS Function | Reference all parent cells to a child cell. |
Copy a Formula with Drag-Fill or Auto-Fill to Save Time
If you have a formula that you’d like to use in multiple cells, without having to manually type the formula in each cell, use the following methods to quickly copy the formula to other areas of your sheet:
- Drag-fill—You can drag from the lower-right corner of a selected cell to copy a formula across contiguous cells in the sheet. As you copy, the formula will automatically change its respective cell references. Check out the Using Copy and Paste article for more information.
- Auto-fill—You can have Smartsheet automatically copy a formula to new, vertically adjacent cells that enter the sheet. When auto-filled, the new formula will automatically change its respective cell references. Learn more about this functionality in our Auto-filling Formulas and Formatting article.
TIP: If you don’t want the cell references in your formula to change as the formula is copied, create absolute cell references.
Video Demo
Check out this video demo on Formulas in Smartsheet.