Skip to content
Work Management Hub Work Management Hub

Expert Reviews, Comparisons & Guides for Smartsheet, Monday.com, Asana, ClickUp & More

Work Management Hub Work Management Hub

Expert Reviews, Comparisons & Guides for Smartsheet, Monday.com, Asana, ClickUp & More

  • Smartsheet
  • ClickUp
  • Asana
  • monday.com
  • Other tools
    • Jira
    • Notion
    • Wrike
    • Airtable
  • Comparisons
  • Guides
  • About
  • Smartsheet
  • ClickUp
  • Asana
  • monday.com
  • Other tools
    • Jira
    • Notion
    • Wrike
    • Airtable
  • Comparisons
  • Guides
  • About
Close

Search

Table comparing Smartsheet formula syntax with Excel habits that fail
How-To GuidesProject ManagementSmartsheet

Smartsheet Formulas Cheat Sheet: 25 Functions With Examples

By Khasim Basha Shaik
May 7, 2026 6 Min Read
0

Home - How-To Guides - 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.

Chart of Smartsheet formula building blocks: @row references, column ranges and common functions
Learn IF, COUNTIF, and @row before you touch INDEX/MATCH. Chart: WorkManagementHub.

Table of Contents

Toggle
  • How Smartsheet formula references work
  • Smartsheet formulas cheat sheet: 25 functions with examples
  • Lookups: INDEX and MATCH vs VLOOKUP
  • Cross-sheet formulas without the mess
  • Date and status formulas I build on almost every project
  • Column formulas vs single-cell formulas
  • Common formula errors and fixes
  • Common questions
    • What is the @row reference in Smartsheet?
    • Does Smartsheet have VLOOKUP?
    • Can a Smartsheet formula use data from another sheet?
    • Why does my formula show #UNPARSEABLE?
    • Does Smartsheet have an IFS function?
  • Sources
  • Related guides

How Smartsheet formula references work

What you wantSmartsheet syntaxExcel habit that fails
This row, one column[Status]@rowA2
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 quotesCurly 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.

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

Desk with laptop and notebook
Write the formula once, convert to a column formula, then test three messy rows.

Common formula errors and fixes

ErrorWhat it usually meansFix
#UNPARSEABLESmartsheet cannot read the formulaCheck brackets, quotes and column names
#NO MATCHA lookup found nothingCheck the value exists, or wrap in IFERROR
#INVALID DATA TYPEText where a number or date is neededChange the column type or use VALUE
#CIRCULAR REFERENCEThe formula points back at itselfMove part of the logic to a helper column
#INVALID COLUMN VALUEThe result does not fit the column typeUse 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.

Sources

  • Smartsheet: Functions list (checked Nov 18, 2026)
  • Smartsheet: MATCH function
  • Smartsheet: VLOOKUP function
  • Smartsheet: COLLECT function
  • Smartsheet: COUNTIFS function

From our team: OneSmar for Smartsheet Teams

OneSMAR for Smartsheet Teams - Available on the App Store

Related guides

Smartsheet or Microsoft Project? The 2026 Licensing Math
Smartsheet
Smartsheet or Microsoft Project? The 2026 Licensing Math
Smartsheet Pricing 2026: License Cost per User, Plans, Who Counts as a Paid Seat, and Add-On Costs
Pricing Guides
Smartsheet Pricing 2026: License Cost per User, Plans, Who Counts as a Paid Seat, and Add-On Costs
Smartsheet for Event Planning in 2026: One Sheet, Budget Columns, Alerts and a Sponsor Dashboard
Smartsheet
Smartsheet for Event Planning in 2026: One Sheet, Budget Columns, Alerts and a Sponsor Dashboard

Tags:

2026automationformulasfunctionsHow-ToSmartsheetspreadsheet
Author

Khasim Basha Shaik

Khasim Basha Shaik is Co-Founder and Managing Director of SSPM Consultants, a Smartsheet Premier Partner based in Bengaluru, India. Since founding SSPM in 2018, he has spent 8+ years on Smartsheet implementation, integration, administration and support for client teams. On Work Management Hub he turns that hands-on work into plain setup guides, fixes and pricing breakdowns, and checks the numbers against official vendor sources.

Follow Me
Other Articles
Table of Smartsheet dashboard limits by plan: Pro has 10 widgets and 1 sheet per report, Business and Enterprise are unlimited
Previous

Smartsheet Dashboards: How to Build One That Holds Up

Table comparing Asana monday and ClickUp AI strengths
Next

Asana vs monday.com vs ClickUp AI 2026: Which Helps Real Work?

No Comment! Be the first one.

    Leave a Reply Cancel reply

    Your email address will not be published. Required fields are marked *

    KB
    Khasim Basha Shaik

    Khasim Basha Shaik is Co-Founder and Managing Director of SSPM Consultants, a Smartsheet Premier Partner based in Bengaluru, India. Since founding SSPM in 2018, he has spent 8+ years on Smartsheet implementation, integration, administration and support for client teams. On Work Management Hub he turns that hands-on work into plain setup guides, fixes and pricing breakdowns, and checks the numbers against official vendor sources.

    About Work Management Hub →

    Popular reviews & guides

    • Table matching ten team types to the project management tool to start with, a runner-up and the reasonBest Project Management Software 2026: 10 Tools Compared by Team Type, Real Monthly Cost and the Limits That Matter
    • Team meeting around a long conference table with laptops openJira Pricing 2026: Standard vs Premium, Annual Tiers and the Oct 13 Increase
    • Developer working on a laptop with code on screen while holding a phoneJira Slack Integration 2026: Setup, @Jira Tickets and Channels Without the Noise
    • Laptop covered in developer stickers on a deskLinear vs GitHub Issues (2026): Who Should Track the Work?
    • Person typing on a laptop at a small round tableLinear-GitHub Sync Broken? Fix Every Failure Mode

    Browse by tool

    • Smartsheet29
    • Monday.com15
    • Asana17
    • ClickUp7
    • Notion18
    • Jira20
    • Wrike6
    • Airtable5
    • Integrations10

    From our team From our team: OneSmar for Smartsheet Teams, our own app

    Independent reviews, comparisons, and hands-on guides for work management tools: Smartsheet, Monday.com, Asana, ClickUp, Notion, Jira, Wrike, and Airtable. Written from daily, real-world use of these platforms.

    Topics

    • Smartsheet
    • Monday.com
    • ClickUp
    • Asana
    • Notion
    • Jira
    • Wrike
    • Airtable
    • Comparisons

    Company

    • About
    • Contact
    • Editorial Policy
    • Author

    Legal

    • Privacy Policy
    • Terms of Use
    • Disclaimer
    • Affiliate Disclosure
    Copyright 2026 Work Management Hub. All rights reserved. Blogsy WordPress Theme