Create efficient formulas with @cell and @row

Users on an Enterprise plan can use AI to generate formulas. 

Users with Editor sharing permissions can only create and edit formulas in unlocked cells on a sheet.

As your sheets get larger, having efficiently created formulas becomes more helpful to their performance. Here’s a look at two functions that help make your formulas more efficient: @cell and @row.

Use @cell to perform smarter cell calculations

When you want to perform calculations in formulas that look at ranges of cells, for example, SUMIF, SUMIFS, COUNTIF, and COUNTIFS, you can use the @cell argument in the criteria of the function. 

The @cell argument performs a calculation on each row at the same time that the primary function (for example, SUMIF) is evaluating the criteria in the range, making your formula more efficient.

For example, using the sample data in the image below, if you wanted to sum the cost of all tasks whose due date occurred in 2023, you would create the following formula:

=SUMIF([Due Date]1:[Due Date]5, YEAR(@cell) = 2023, Cost1:Cost5)

Brandfolder Image

Add the IFERROR function to your formula to ensure that you don't get an error if @cell encounters a blank cell in the formula. For example: =COUNTIF(Date:Date, IFERROR(YEAR(@cell), 0) = 2016)

Use @row to improve formula performance

With @row, you can improve performance with the automatic changing of cell references in a column (for example, the status of your tasks) for a large number of rows. 

Let’s say we want to use a formula to change symbols in the status column of this grid automatically:

Brandfolder Image

Here’s an example of a formula you might build to automate the RYG ball color change in your Status column:

=IF(Complete3 <> 1, IF(TODAY() - [Due Date]3 > 0, "Red", IF(TODAY() - [Due Date]3 > -3, "Yellow", "Green")))

The formula above contains multiple references to cells in row 3. When you add a row above row 3, Smartsheet automatically updates all cell references in the formula to row 4. Smartsheet applies the same formula modification to every row below. As the number of formulas that require updates increases, Smartsheet performance is impacted.

To possibly improve performance in your sheets, substitute the row numbers with @row in your cell references. This is how the formula looks using @row:

=IF(Complete@row <> 1, IF(TODAY() - [Due Date]@row > 0, "Red", IF(TODAY() - [Due Date]@row > -3, "Yellow", "Green")))

Smartsheet doesn't need to update cell references when a row is moved, resulting in quicker sheet load. You can also copy and paste this formula without having to manually change the row numbers on cell references, saving you time when you need to copy your 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.

Ask the Community