
Smartsheet Formulas Cheat Sheet: 25 Functions With Examples
This Smartsheet formulas cheat sheet covers the 25 functions I use most when building sheets for clients, each with a copy-ready example. Smartsheet formulas start with = like Excel, but you point at cells by column name, such as [Status]@row, not A1. That one difference breaks most formulas pasted from Excel.
I checked every function name and syntax against Smartsheet’s Functions list on November 18, 2026. Formulas work on Pro, Business and Enterprise. Owners and Admins can edit any formula. Editors can add formulas in cells that are not locked.

How Smartsheet formula references work
| What you want | Smartsheet syntax | Excel habit that fails |
|---|---|---|
| This row, one column | [Status]@row | A2 |
| A whole column | [Status]:[Status] | A:A |
| A fixed cell | [Budget]$1 | $A$1 (similar idea) |
| Another sheet | {Budget Range} | Sheet2!A:A |
| Not equal | <> | != (fails) |
| Text | “Done” in straight quotes | Curly quotes pasted from Word fail |
Two rules save the most time. Use @row instead of row numbers, because it keeps working when rows move. And if a column name has a space or a number, keep the square brackets, like [End Date]@row.
Smartsheet formulas cheat sheet: 25 functions with examples
Copy a formula, then change the column names to match your sheet.
| Function | Example | Use it to |
|---|---|---|
| IF | =IF([Status]@row = "Done", 1, 0) | Return one value or another |
| AND / OR | =IF(AND([Due]@row < TODAY(), [Status]@row <> "Done"), "Late") | Test two or more conditions |
| IFERROR | =IFERROR([Cost]@row / [Hours]@row, 0) | Hide errors with a fallback value |
| ISBLANK | =IF(ISBLANK([Owner]@row), "Assign me") | Find empty cells |
| SUM | =SUM([Budget]:[Budget]) | Add a whole column |
| SUMIF | =SUMIF([Region]:[Region], "West", [Budget]:[Budget]) | Add only rows that match |
| SUMIFS | =SUMIFS([Budget]:[Budget], [Region]:[Region], "West", [Status]:[Status], "Open") | Add rows that match several rules |
| COUNTIF | =COUNTIF([Status]:[Status], "Blocked") | Count rows that match |
| COUNTIFS | =COUNTIFS([Status]:[Status], "Open", [Owner]:[Owner], "Priya") | Count with several rules |
| COUNTM | =COUNTM([Assigned To]@row) | Count items in a multi-select or multi-contact cell |
| AVG / AVERAGEIF | =AVERAGEIF([Type]:[Type], "Bug", [Days]:[Days]) | Average, with or without a rule |
| MAX / MIN | =MAX([End Date]:[End Date]) | Latest date or biggest number |
| ROUND | =ROUND([Hours]@row * 1.15, 1) | Round to set decimals |
| TODAY | =TODAY(7) | Today, or today plus days (here, 7) |
| NETWORKDAYS | =NETWORKDAYS([Start]@row, [End]@row) | Working days between two dates |
| WORKDAY | =WORKDAY([Start]@row, 10) | Date after a number of working days |
| YEAR / MONTH / WEEKNUMBER | =WEEKNUMBER([Due]@row) | Pull a part of a date for reports |
| INDEX + MATCH | =INDEX([Owner]:[Owner], MATCH([ID]@row, [ID]:[ID], 0)) | Look up a value, left or right |
| VLOOKUP | =VLOOKUP([ID]@row, [ID]1:[Owner]50, 2, false) | Older lookup. Always use false |
| COLLECT | =INDEX(COLLECT([Owner]:[Owner], [Region]:[Region], "West"), 1) | Filter a range inside another function |
| JOIN | =JOIN(COLLECT([Task]:[Task], [Status]:[Status], "Blocked"), ", ") | Combine values into one text cell |
| CONTAINS / HAS | =IF(HAS([Tags]@row, "Urgent"), 1, 0) | Check text, or one item in a multi-select |
| LEFT / LEN / FIND / SUBSTITUTE | =LEFT([Code]@row, FIND("-", [Code]@row) - 1) | Clean up text and IDs |
| VALUE | =VALUE([Text Number]@row) | Turn text into a number |
| CHILDREN / PARENT / ANCESTORS | =SUM(CHILDREN()) | Roll up and indent logic in hierarchy sheets |
Lookups: INDEX and MATCH vs VLOOKUP
VLOOKUP works, but it has two traps. It can only look to the right of the first column. And its last argument defaults to true, which means an approximate match on sorted data, per Smartsheet’s VLOOKUP page. If your data is not sorted, you get the wrong row with no error.
INDEX and MATCH fix both problems. MATCH finds the row number, and INDEX returns the value from any column in that row. Always add 0 as the last MATCH argument for an exact match. Smartsheet’s default for MATCH is also approximate, as its MATCH page explains.
For a lookup with more than one condition, wrap COLLECT inside INDEX. This returns the first owner in the West region who has an open task:
=INDEX(COLLECT([Owner]:[Owner], [Region]:[Region], "West", [Status]:[Status], "Open"), 1)Cross-sheet formulas without the mess
To pull data from another sheet, start typing the formula, then select Reference another sheet in the formula help. Pick the sheet, select the columns, and name the reference. It shows in curly braces, like {Projects Owner}.
- Reference whole columns, not a block of cells. New rows in the source sheet are then included.
- Name references by sheet and column, like {Projects Owner}. Six months later you will know what it points at.
- Use them for lookups and totals, not to copy whole sheets. For moving lots of data, use reports or Data Shuttle.
- Expect #NO MATCH when the value does not exist in the other sheet. Wrap the formula in IFERROR to show a blank instead.
Date and status formulas I build on almost every project
Red, yellow, green from the due date:
=IF(ISBLANK([End Date]@row), "No date", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row <= TODAY(7), "Yellow", "Green")))Days late, counting only working days:
=IF([End Date]@row < TODAY(), NETWORKDAYS([End Date]@row, TODAY()) - 1, 0)Percent complete on a parent row:
=IF(COUNT(CHILDREN([Task Name]@row)) = 0, [% Complete]@row, AVG(CHILDREN()))One warning. If dependencies are on in the project settings, you cannot put formulas in Start Date, End Date, Duration, % Complete or Predecessors. Smartsheet controls those columns. Put your logic in a helper column instead. Our Gantt chart setup guide shows the column setup.
Column formulas vs single-cell formulas
If a formula should apply to every row, right-click the cell and choose Convert to Column Formula. New rows then get it by themselves, and nobody can overwrite one cell by mistake. Use single-cell formulas only for parent rows and sheet summary fields.
Sheet summary fields are the best home for numbers that feed a dashboard, like open tasks or total budget. Keep heavy COUNTIFS there, not on every row, so large sheets stay fast.

Common formula errors and fixes
| Error | What it usually means | Fix |
|---|---|---|
| #UNPARSEABLE | Smartsheet cannot read the formula | Check brackets, quotes and column names |
| #NO MATCH | A lookup found nothing | Check the value exists, or wrap in IFERROR |
| #INVALID DATA TYPE | Text where a number or date is needed | Change the column type or use VALUE |
| #CIRCULAR REFERENCE | The formula points back at itself | Move part of the logic to a helper column |
| #INVALID COLUMN VALUE | The result does not fit the column type | Use a Text/Number column for the result |
For a step-by-step fix of each error, see our guide to Smartsheet formulas not working. If you use AI to draft a formula, treat it like a first draft. Check every @row, every bracket and every column name, then test it on three messy rows before you make it a column formula. Our Smartsheet AI guide covers which plans include AI formula help.
Formulas also drive good automations. A status formula plus a workflow that alerts the owner when it turns Red is one of the simplest wins in Smartsheet. Our Smartsheet automations setup guide shows how to build it, and the Smartsheet pricing guide shows what each plan includes.
Common questions
What is the @row reference in Smartsheet?
@row points at the cell in the same row as the formula, like [Status]@row. It is faster than a row number and keeps working when rows move or are added.
Does Smartsheet have VLOOKUP?
Yes. The syntax is VLOOKUP(search_value, lookup_table, column_num, [match_type]). The default match type is true, which is approximate, so set it to false for an exact match. Most builders use INDEX and MATCH instead.
Can a Smartsheet formula use data from another sheet?
Yes. You create a cross-sheet reference, which shows in the formula in curly braces, like {Budget Range}. Lookups such as INDEX, MATCH, COLLECT and SUMIFS work with these references.
Why does my formula show #UNPARSEABLE?
Smartsheet cannot read the formula. The usual causes are a missing bracket or quote, an Excel-style A1 reference, or a column name that was renamed. Check the column names first.
Does Smartsheet have an IFS function?
No. Smartsheet uses nested IF statements instead, like IF(a, 1, IF(b, 2, 3)). Keep the nesting short or split the logic into a helper column.



