Smartsheet Formulas & Functions

Key Differences From Excel

Smartsheet formulas start with = and look similar to Excel, but there are critical differences you need to internalize before writing your first formula.

Column Names Instead of Cell Addresses

Excel uses cell addresses (B2, C5). Smartsheet uses column names in brackets:

Row Context with @row

Formulas operate in row context by default. When you place a formula in a column, it automatically applies to the current row. The @row suffix makes this explicit:

=[Revenue]@row - [Cost]@row    // Calculates for this row
=[Status]@row                  // Returns Status column value for this row

Column Range Syntax (Same Sheet)

To reference an entire column (all rows), use column:column syntax:

[Status]:[Status]              // All rows in the Status column
[Budget]:[Budget]              // All rows in the Budget column

No Array Formulas or Dynamic Arrays

Smartsheet does not support Excel's dynamic array functions (FILTER, UNIQUE, SORT, SEQUENCE). For multi-row filtering, use COLLECT() (covered below) or create a Report.

Formula in Column Types

Formulas can only be placed in non-system columns. You can't put a formula in system-managed columns (Created Date, Created By, Modified Date, Modified By) or in the Primary Column when it has hierarchy.

Essential Functions

Lookups

VLOOKUP — looks up a value across a range or cross-sheet reference:

=VLOOKUP([Product ID]@row, {Products Range}, 2, false)
// search_value, lookup_range, column_index, exact_match

INDEX + MATCH — more flexible than VLOOKUP, looks in any column:

=INDEX({Price Range}, MATCH([Product ID]@row, {Product ID Range}, 0))

COLLECT — Smartsheet's Filter Function

COLLECT is unique to Smartsheet. It returns an array of values from one column where a corresponding column meets criteria. Wrap it in INDEX to return a single value, or JOIN to create a delimited string:

// Return the first active project manager
=INDEX(COLLECT({PM Name}, {Status}, "Active"), 1)

// Return all names with "Active" status as a comma-separated list
=JOIN(COLLECT({Name Range}, {Status Range}, "Active"), ", ")

JOIN

Combines values into a delimited string. Most useful with COLLECT:

=JOIN([Tags]:[Tags], ", ")
=JOIN(COLLECT([Name]:[Name], [Team]:[Team], "Engineering"), ", ")

Multi-Criteria Aggregation

COUNTIFS — count rows matching multiple conditions:

=COUNTIFS([Status]:[Status], "At Risk", [Priority]:[Priority], "High")

SUMIFS — sum values matching multiple conditions:

=SUMIFS([Budget]:[Budget], [Department]:[Department], "Engineering", [Status]:[Status], "Approved")

AVGIFS — average values matching multiple conditions:

=AVGIFS([Score]:[Score], [Team]:[Team], "Sales", [Month]:[Month], "January")

Hierarchy-Aware Functions

These are unique to Smartsheet and work with row hierarchies:

=SUM(CHILDREN())          // Sum direct child row values
=PARENT([Status]@row)     // Return the parent row's Status value
=ANCESTORS([Dept]@row)    // Return array of all ancestor values

CHILDREN() is typically placed on a parent row. PARENT() and ANCESTORS() are used on child rows to inherit context from their parent.

Text Functions

HAS — check if a cell contains a value (works with Multi Select columns):

=HAS([Tags]@row, "Urgent")           // Returns true if "Urgent" is in Tags
=IF(HAS([Skills]@row, "Python"), 1, 0)

CONTAINS — check if text exists within a string:

=CONTAINS("Manager", [Title]@row)

Standard text functions work the same as Excel: LEFT(), RIGHT(), MID(), LEN(), TRIM(), UPPER(), LOWER(), SUBSTITUTE()

Date Functions

=TODAY()                           // Current date
=NETWORKDAYS([Start]@row, [End]@row)  // Business days between dates
=YEAR([Date]@row)                  // Extract year
=MONTH([Date]@row)                 // Extract month (1-12)
=DAY([Date]@row)                   // Extract day of month

Error Handling

=IFERROR([Budget]@row / [Hours]@row, 0)        // Return 0 on error
=IFERROR(VLOOKUP(...), "Not found")            // Return text on #NO MATCH

The AI Formula Generator

Smartsheet's AI formula generator is one of the most practical AI features in the platform today.

How to access: Click the AI sparkle icon in the formula bar, or open the AI Tools panel → Generate Formulas.

How to use it: Describe what you want in plain language. The AI understands your sheet's column names and types.

Examples:

• "Calculate the number of working days between Start Date and End Date" → =NETWORKDAYS([Start Date]@row, [End Date]@row)
• "Sum the Budget column only for rows where Department is Marketing" → =SUMIFS([Budget]:[Budget], [Department]:[Department], "Marketing")
• "Count how many rows have Status as At Risk or Blocked" → =COUNTIFS([Status]:[Status], OR(@cell="At Risk", @cell="Blocked"))
• "Show Overdue if Due Date is in the past and % Complete is less than 1" → =IF(AND([Due Date]@row < TODAY(), [% Complete]@row < 1), "Overdue", "On Track")

What the AI does well: Formula syntax, function combinations, multi-criteria conditions, date arithmetic. It maps your column names correctly because it has access to your sheet's structure.

What to verify: Always check the generated formula logic before applying it to a full column. The AI may misinterpret ambiguous requests or combine functions in unexpected ways.

Smartsheet reports that formula support inquiries dropped approximately 50% since launching this feature.

Common Mistakes