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 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
- Reference Data in Another Sheet
- Summary of Reference Types
- Reference Children, Parents, and Ancestors with Hierarchy Functions
- Perform Calculations with Dates
- Working with Percentages
- Drag-Fill and Auto-Fill to Save Time
- Cells that Can't Contain Formulas
- Features that Don't Allow Formula Editing
- Video Demo

Note that function delimiters will differ depending on your language settings. In languages where a comma is used as a decimal separator, the function delimiter is a semicolon.

### 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 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 Data in Another Sheet

You can create a reference to an individual cell, ranges of cells, or entire columns. For more information on referencing data from other sheets, read our Formulas: Reference Data from Other Sheets article.

#### Reference Individual Cells

While building your formula, you can **select a cell** to reference it and work with that cell’s data in your formula. (You can also *manually* type the column name and row number to reference the cell.)

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 VLOOKUP 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 VLOOKUP 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) |

An individual cell, range of cells, or complete columns from another sheet | Type the name of a previously created reference to another sheet, wrapped in curly braces. | =COUNT({my_sheet1 Range1}) |

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

### Perform Calculations with Dates

You can place a formula in one date cell that adds or subtracts numbers from dates in other cells. Numbers are treated as days, unless date functions are used. More information on date functions can be found in the Functions List.

NOTE: You can't place formulas in date columns being used for dependencies. See Enabling Dependencies & Using Predecessors for more on dependencies.

The following table contains examples of using dates in formulas. (The dates in the table below are in mm/dd/yy format.)

Date | Formula | Description | Result | |
---|---|---|---|---|

1 | 5/19/17 | =Date1 - 5 | Subtracts 5 days from the date | 5/12/17 |

2 | 12/10/17 | =Date2 + 5 | Adds 5 days to the date | 12/15/17 |

3 | 1/20/18 | =DATE(YEAR(date12), MONTH(date12) + 1, DAY(date12)) | Add a month to the date with use of the DATE, YEAR, MONTH, and DAY functions | 2/20/18 |

### Work with Percentages

Smartsheet treats percentages as values between 0 and 1. When creating formulas in columns formatted for percent (using the % 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.

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

### Columns and Features that Can't Contain Formulas

The following columns and features of your sheet *cannot* contain formulas.

#### Columns

- Contact columns (for example, Assigned To)
- System columns (for example, Modified By)
- Default columns on the right side of the sheet (attachments, comments, row action indicator)
- Any column being used for
**dependencies**and**resource management**, such as:- Start Date
- End Date
- Duration
- Predecessors
- % Complete
- % Allocation

#### Features

- Forms
- Update requests (Formula results can still be viewed in update requests, but they can't contain standalone formulas.)
- Reports (Formula results can still be viewed in reports, but they can't contain standalone formulas.)
- Smartsheet Sights™ (Formula results can still be viewed in Sights, but they can't contain standalone formulas.)

### Features that Don't Allow Formula Editing

Formulas can't be edited from the following features in Smartsheet:

- Forms
- Update requests (Formula results can still be viewed in update requests, but the formulas can't be edited.)
- Reports (Formula results can still be viewed in reports, but the formulas can't be edited.)
- Sights (Formula results can still be viewed in Sights, but the formulas can't be edited.)