Smartsheet formula cheat sheet: 60+ formulas, and how each one runs in Wisegrid.

Return one value when a condition is true, another when it is false.

=IF([Status]@row = "Done", "Complete", "In progress")

=IF([Status]@row="Done","Complete","In progress")

Test several conditions in order and return the first match.

=IFS([Score]@row >= 90, "A", [Score]@row >= 80, "B", true, "C")

=IFS([Score]@row>=90,"A",[Score]@row>=80,"B",true,"C")

True only when every condition is true.

=IF(AND([Approved]@row, [Funded]@row), "Go", "Hold")

=IF(AND([Approved]@row,[Funded]@row),"Go","Hold")

True when any one condition is true.

=IF(OR([Priority]@row = "High", [Overdue]@row), "Escalate", "")

=IF(OR([Priority]@row="High",[Overdue]@row),"Escalate","")

Reverse a true/false value.

=IF(NOT([Done]@row), "Open", "Closed")

=IF(NOT([Done]@row),"Open","Closed")

Return a fallback value instead of an error result.

=IFERROR([Revenue]@row / [Units]@row, 0)

=IFERROR([Revenue]@row/[Units]@row,0)

Return a fallback only when the result is a "no match" error.

=IFNA(VLOOKUP([Email]@row, {Customers}, 2, false), "Unknown")

=IFNA(VLOOKUP([Email]@row,[Customers]!A:Z,2,false),"Unknown")

Cross-sheet {Reference} objects resolve to your target sheet on import. In Wisegrid you can also type them inline as [Sheet]!range, with no picker modal to click through.

Match a value against a list of cases and return the paired result.

=SWITCH([Tier]@row, "Gold", 3, "Silver", 2, 1)

=SWITCH([Tier]@row,"Gold",3,"Silver",2,1)

True when a piece of text appears inside a cell (useful for multi-select).

=IF(CONTAINS("Urgent", [Tags]@row), "!", "")

=IF(CONTAINS("Urgent",[Tags]@row),"!","")

True when a multiple-selection cell contains a specific value.

=IF(HAS([Labels]@row, "Blocked"), "Review", "")

=IF(HAS([Labels]@row,"Blocked"),"Review","")

True when a cell has no value.

=IF(ISBLANK([Owner]@row), "Unassigned", [Owner]@row)

=IF(ISBLANK([Owner]@row),"Unassigned",[Owner]@row)

True when a cell holds a valid date.

=IF(ISDATE([Due]@row), "Scheduled", "Set a date")

=IF(ISDATE([Due]@row),"Scheduled","Set a date")

True when a cell holds a number.

=IF(ISNUMBER([Estimate]@row), [Estimate]@row, 0)

=IF(ISNUMBER([Estimate]@row),[Estimate]@row,0)

True when a cell holds text.

=IF(ISTEXT([Code]@row), UPPER([Code]@row), "")

=IF(ISTEXT([Code]@row),UPPER([Code]@row),"")

Add up numbers, either down a column or across a row hierarchy.

=SUM(CHILDREN([Hours]@row))

=SUM(CHILDREN("Hours"))

Wisegrid stores this in its own equivalent form (for example, hierarchy functions take the column as a quoted name). The importer rewrites it for you, so you do not edit anything by hand.

Add up values where a matching column meets one criterion.

=SUMIF([Status]:[Status], "Done", [Hours]:[Hours])

=SUMIF(A:A,"Done",B:B)

Whole-column references map to your sheet's real columns on import. The letters shown are example positions in first-seen order, not the final ones.

Add up values that meet several criteria at once.

=SUMIFS([Amount]:[Amount], [Region]:[Region], "West", [Status]:[Status], "Closed")

=SUMIFS(A:A,B:B,"West",C:C,"Closed")

Whole-column references map to your sheet's real columns on import. The letters shown are example positions in first-seen order, not the final ones.

Count how many cells contain numbers.

=COUNT([Amount]:[Amount])

=COUNT(A:A)

Whole-column references map to your sheet's real columns on import. The letters shown are example positions in first-seen order, not the final ones.

Count cells that meet one criterion.

=COUNTIF([Status]:[Status], "Done")

=COUNTIF(A:A,"Done")

Whole-column references map to your sheet's real columns on import. The letters shown are example positions in first-seen order, not the final ones.

Count cells that meet several criteria at once.

=COUNTIFS([Region]:[Region], "West", [Status]:[Status], "Open")

=COUNTIFS(A:A,"West",B:B,"Open")

Whole-column references map to your sheet's real columns on import. The letters shown are example positions in first-seen order, not the final ones.

Count the individual selections inside a multiple-selection cell.

=COUNTM([Labels]@row)

=COUNTM([Labels]@row)

Average a set of numbers (Smartsheet spells AVERAGE as AVG).

=AVG([Score]:[Score])

=AVG(A:A)

Whole-column references map to your sheet's real columns on import. The letters shown are example positions in first-seen order, not the final ones.

The largest value in a range.

=MAX([Bid]:[Bid])

=MAX(A:A)

Whole-column references map to your sheet's real columns on import. The letters shown are example positions in first-seen order, not the final ones.

The smallest value in a range.

=MIN([Start]:[Start])

=MIN(A:A)

Whole-column references map to your sheet's real columns on import. The letters shown are example positions in first-seen order, not the final ones.

The middle value of a range.

=MEDIAN([Cycle Time]:[Cycle Time])

=MEDIAN(A:A)

Whole-column references map to your sheet's real columns on import. The letters shown are example positions in first-seen order, not the final ones.

Gather every value that meets a criterion, often wrapped in JOIN or AVG.

=JOIN(COLLECT([Task]:[Task], [Status]:[Status], "Open"), ", ")

=JOIN(COLLECT(A:A,B:B,"Open"),", ")

Whole-column references map to your sheet's real columns on import. The letters shown are example positions in first-seen order, not the final ones.

Reduce a range to its unique values, often to count them.

=COUNT(DISTINCT([Owner]:[Owner]))

=COUNT(DISTINCT(A:A))

Whole-column references map to your sheet's real columns on import. The letters shown are example positions in first-seen order, not the final ones.

The Nth largest value in a range.

=LARGE([Revenue]:[Revenue], 3)

=LARGE(A:A,3)

Whole-column references map to your sheet's real columns on import. The letters shown are example positions in first-seen order, not the final ones.

The Nth smallest value in a range.

=SMALL([Due]:[Due], 1)

=SMALL(A:A,1)

Whole-column references map to your sheet's real columns on import. The letters shown are example positions in first-seen order, not the final ones.

Find a row by matching the first column, return a value from another column.

=VLOOKUP([Email]@row, {Customers}, 2, false)

=VLOOKUP([Email]@row,[Customers]!A:Z,2,false)

Cross-sheet {Reference} objects resolve to your target sheet on import. In Wisegrid you can also type them inline as [Sheet]!range, with no picker modal to click through.

Like VLOOKUP but matches across a row instead of down a column.

=HLOOKUP([Month]@row, {Targets}, 2, false)

=HLOOKUP([Month]@row,[Targets]!A:Z,2,false)

Cross-sheet {Reference} objects resolve to your target sheet on import. In Wisegrid you can also type them inline as [Sheet]!range, with no picker modal to click through.

Return the value at a given position in a range, often paired with MATCH.

=INDEX({Prices}, MATCH([SKU]@row, {SKUs}, 0))

=INDEX([Prices]!A:Z,MATCH([SKU]@row,[SKUs]!A:Z,0))

Cross-sheet {Reference} objects resolve to your target sheet on import. In Wisegrid you can also type them inline as [Sheet]!range, with no picker modal to click through.

Return the position of a value within a range.

=MATCH([SKU]@row, {SKUs}, 0)

=MATCH([SKU]@row,[SKUs]!A:Z,0)

Cross-sheet {Reference} objects resolve to your target sheet on import. In Wisegrid you can also type them inline as [Sheet]!range, with no picker modal to click through.

A single, bidirectional lookup. In Wisegrid this replaces the VLOOKUP/HLOOKUP split.

=LOOKUP([SKU]@row, {SKUs}, {Prices})

=LOOKUP([SKU]@row,[SKUs]!A:Z,[Prices]!A:Z)

Cross-sheet {Reference} objects resolve to your target sheet on import. In Wisegrid you can also type them inline as [Sheet]!range, with no picker modal to click through.

Match a value in one range and return the aligned value from another.

=XLOOKUP([SKU]@row, {SKUs}, {Prices})

=XLOOKUP([SKU]@row,[SKUs]!A:Z,[Prices]!A:Z)

Cross-sheet {Reference} objects resolve to your target sheet on import. In Wisegrid you can also type them inline as [Sheet]!range, with no picker modal to click through.

A more flexible MATCH, returning the position of a value in a range.

=XMATCH([SKU]@row, {SKUs})

=XMATCH([SKU]@row,[SKUs]!A:Z)

Cross-sheet {Reference} objects resolve to your target sheet on import. In Wisegrid you can also type them inline as [Sheet]!range, with no picker modal to click through.

Pick one value from a list by its index number.

=CHOOSE([Rank]@row, "Gold", "Silver", "Bronze")

=CHOOSE([Rank]@row,"Gold","Silver","Bronze")

The first N characters of some text.

=LEFT([Code]@row, 3)

=LEFT([Code]@row,3)

The last N characters of some text.

=RIGHT([Phone]@row, 4)

=RIGHT([Phone]@row,4)

A slice of text starting at a given position.

=MID([SKU]@row, 4, 2)

=MID([SKU]@row,4,2)

The number of characters in a piece of text.

=LEN([Description]@row)

=LEN([Description]@row)

The position of one piece of text inside another (case sensitive).

=FIND("-", [Ref]@row)

=FIND("-",[Ref]@row)

The position of one piece of text inside another (case insensitive).

=SEARCH("west", [Region]@row)

=SEARCH("west",[Region]@row)

Replace part of a text string by position.

=REPLACE([Phone]@row, 1, 3, "555")

=REPLACE([Phone]@row,1,3,"555")

Swap every occurrence of one substring for another.

=SUBSTITUTE([Path]@row, "\", "/")

=SUBSTITUTE([Path]@row,"\", "/")

Convert text to upper case.

=UPPER([Code]@row)

=UPPER([Code]@row)

Convert text to lower case.

=LOWER([Email]@row)

=LOWER([Email]@row)

Strip leading, trailing, and repeated spaces.

=TRIM([Name]@row)

=TRIM([Name]@row)

Combine a range of values into one string with a separator.

=JOIN([Tags]@row, ", ")

=JOIN([Tags]@row,", ")

Convert text that looks like a number into an actual number.

=VALUE([Amount Text]@row)

=VALUE([Amount Text]@row)

Join text with the ampersand operator, the same as in Smartsheet.

=[First]@row & " " & [Last]@row

=[First]@row&" "&[Last]@row

Today's date, recalculated each day.

=IF([Due]@row < TODAY(), "Overdue", "")

=IF([Due]@row<TODAY(),"Overdue","")

Build a date from a year, month, and day.

=DATE(2026, 7, 1)

=DATE(2026,7,1)

The day-of-month number from a date.

=DAY([Due]@row)

=DAY([Due]@row)

The month number from a date.

=MONTH([Due]@row)

=MONTH([Due]@row)

The year number from a date.

=YEAR([Due]@row)

=YEAR([Due]@row)

The day-of-week number for a date.

=WEEKDAY([Due]@row)

=WEEKDAY([Due]@row)

The week number of the year for a date.

=WEEKNUMBER([Due]@row)

=WEEKNUMBER([Due]@row)

Count the working days between two dates, skipping weekends.

=NETWORKDAY([Start]@row, [End]@row)

=NETWORKDAY([Start]@row,[End]@row)

The date that is N working days after a start date.

=WORKDAY([Start]@row, 10)

=WORKDAY([Start]@row,10)

Strip the time portion off a date-time value.

=DATEONLY([Logged At]@row)

=DATEONLY([Logged At]@row)

The absolute (non-negative) value of a number.

=ABS([Variance]@row)

=ABS([Variance]@row)

Round a number to a given number of decimal places.

=ROUND([Total]@row, 2)

=ROUND([Total]@row,2)

Round a number up, away from zero.

=ROUNDUP([Hours]@row, 0)

=ROUNDUP([Hours]@row,0)

Round a number down, toward zero.

=ROUNDDOWN([Hours]@row, 0)

=ROUNDDOWN([Hours]@row,0)

The integer part of a number.

=INT([Days]@row)

=INT([Days]@row)

The remainder after division.

=MOD([Index]@row, 2)

=MOD([Index]@row,2)

Raise a number to a power.

=POWER([Base]@row, 2)

=POWER([Base]@row,2)

The square root of a number.

=SQRT([Area]@row)

=SQRT([Area]@row)

Round a number up to the nearest multiple.

=CEILING([Total]@row, 5)

=CEILING([Total]@row,5)

Round a number down to the nearest multiple.

=FLOOR([Total]@row, 5)

=FLOOR([Total]@row,5)

The direct child rows of the current row, for rolling values up.

=SUM(CHILDREN([Hours]@row))

=SUM(CHILDREN("Hours"))

Wisegrid stores this in its own equivalent form (for example, hierarchy functions take the column as a quoted name). The importer rewrites it for you, so you do not edit anything by hand.

The parent row of the current row.

=PARENT([Name]@row)

=PARENT("Name")

Wisegrid stores this in its own equivalent form (for example, hierarchy functions take the column as a quoted name). The importer rewrites it for you, so you do not edit anything by hand.

Every row above the current row in the outline.

=COUNT(ANCESTORS([Name]@row))

=COUNT(ANCESTORS("Name"))

Wisegrid stores this in its own equivalent form (for example, hierarchy functions take the column as a quoted name). The importer rewrites it for you, so you do not edit anything by hand.

Every row nested under the current row at any depth.

=SUM(DESCENDANTS([Hours]@row))

=SUM(DESCENDANTS("Hours"))

Wisegrid stores this in its own equivalent form (for example, hierarchy functions take the column as a quoted name). The importer rewrites it for you, so you do not edit anything by hand.

Smartsheet's named cross-sheet reference. It resolves to your target sheet on import, and in Wisegrid you can also type refs inline as [Sheet]!range.

=SUMIF({Invoices Region}, "West", {Invoices Amount})

=SUMIF([Invoices Region]!A:Z,"West",[Invoices Amount]!A:Z)

Cross-sheet {Reference} objects resolve to your target sheet on import. In Wisegrid you can also type them inline as [Sheet]!range, with no picker modal to click through.