Smartsheet formulas, known as functions, enable you to perform a wide range of calculations and data manipulations to enhance your workflow efficiency and reporting.
Below is the complete list of Smartsheet formulas, their syntax, descriptions, and examples to help you leverage their full potential.
Never Struggle With Smartsheet Formulas Again
Leverage My Smartsheet Training
Not sure how to write formulas?
My training courses will teach you how to write every formula you will ever need.
Get StartedFunction Assist
Don’t have the time to learn formulas?
Would you rather use a generator that creates or troubleshoots your faulty formulas instantly (in seconds)?
Then Function Assist is for you.
Learn MoreComplete Smartsheet Formula List
Formula | Syntax | Description | Example |
---|---|---|---|
ABS | ABS(number) | Returns the absolute value of a number. | ABS(-10) returns 10 |
ANCESTORS | ANCESTORS([reference]) | References all of the ancestors in a cell’s hierarchy within another function. | ANCESTORS([Task ID]) |
AND | AND(logical_expression1, [logical_expression2, ...]) | Returns TRUE if all provided logical expressions are true. | AND([Status]@row = "Complete", [Priority]@row > 3) |
AVERAGEIF | AVERAGEIF(range, criterion, [average_range]) | Returns the average of a range of numbers that meet a given criteria. | AVERAGEIF([Score]:[Score], ">70", [Score]:[Score]) |
AVG | AVG(number1, [number2, ...]) | Returns the average (mean) of the provided numbers. | AVG(10, 20, 30) returns 20 |
AVGW | AVGW(range, range_weight) | Returns a weighted average where each value is weighted by its relative importance. | AVGW([Scores]:[Scores], [Weights]:[Weights]) |
CEILING | CEILING(number, [multiple]) | Rounds a number away from zero to the nearest specified multiple of significance. | CEILING(4.2, 1) returns 5 |
CHAR | CHAR(number) | Converts a number into a character. | CHAR(65) returns A |
CHILDREN | CHILDREN([reference]) | References the child rows of the referenced parent row within another function. | CHILDREN([Parent Task]) |
COLLECT | COLLECT(range, criterion_range1, criterion1, ...) | Selects specific values in a range that meet the provided criteria within another function. | COLLECT([Names]:[Names], [Status]:[Status], "Active") |
CONTAINS | CONTAINS(search_for, range) | Searches for a character or string and returns TRUE if found, otherwise FALSE . | CONTAINS("Pro", [Project Name]:[Project Name]) |
COUNT | COUNT(value1, [value2, ...]) | Counts non-blank values. | COUNT([ID]:[ID]) |
COUNTIF | COUNTIF(range, criterion) | Counts the number of cells within a range that meet a criterion. | COUNTIF([Status]:[Status], "Complete") |
COUNTIFS | COUNTIFS(range1, criterion1, [range2, criterion2, ...]) | Counts the number of times all given conditions in their respective ranges are met. | COUNTIFS([Status]:[Status], "Complete", [Priority]:[Priority], ">3") |
COUNTM | COUNTM(search_range1, [search_range2, ...]) | Counts the number of elements in a multicontact or multiselect dropdown column cell or cell range. | COUNTM([Contacts]:[Contacts]) |
DATE | DATE(year, month, day) | Combines values for a year, month, and day into a date. | DATE(2025, 1, 17) returns 01/17/2025 |
DATEONLY | DATEONLY(date_time) | Extracts the date portion of a date/time value in a Date column. | DATEONLY([Start Date Time]) |
DAY | DAY(date) | Returns the day of the month, 1–31, from a date. | DAY("2025-01-17") returns 17 |
DECTOHEX | DECTOHEX(number) | Converts a decimal number into a hexadecimal value. | DECTOHEX(255) returns FF |
DESCENDANTS | DESCENDANTS([parent_cell]) | References all descendant rows of the parent row’s referenced cell within another function. | DESCENDANTS([Parent Task]) |
DISTINCT | DISTINCT(range) | Returns an array of unique values in a range within another function. | DISTINCT([Category]:[Category]) |
FIND | FIND(search_for, text_to_search, [start_position]) | Returns the starting position of a string within text. | FIND("Smith", [Name]:[Name], 1) |
FLOOR | FLOOR(number, multiple) | Rounds a number to the nearest specified multiple of significance. | FLOOR(4.7, 1) returns 4 |
HAS | HAS(search_range, criterion) | Searches for an exact match of a single value and returns TRUE if found, otherwise FALSE . | HAS([Status]:[Status], "Approved") |
HEXTODEC | HEXTODEC(hex_string) | Converts a hexadecimal value to a decimal number. | HEXTODEC("FF") returns 255 |
IF | IF(logical_expression, value_if_true, [value_if_false]) | Evaluates a logical expression and returns one value if true or another if false. | IF([Score]@row > 70, "Pass", "Fail") |
IFERROR | IFERROR(value, value_if_error) | Returns the first value if it isn’t an error; otherwise, returns the second value. | IFERROR([Result]@row, "Error") |
INDEX | INDEX(range, row_index, [column_index]) | Returns a value from a range based on provided row and column indexes. | INDEX([Data]:[Data], 2, 3) |
INT | INT(value) | Returns the integer portion of a number. | INT(4.7) returns 4 |
ISBLANK | ISBLANK(value) | Checks whether a cell value is blank. | ISBLANK([Comments]@row) |
ISBOOLEAN | ISBOOLEAN(value) | Checks whether a value is a boolean (e.g., checkbox, flag, or star). | ISBOOLEAN([Approval]@row) |
ISCRITICAL | ISCRITICAL(value) | Identifies if a row is on the critical path. | ISCRITICAL([Task ID]@row) |
ISDATE | ISDATE(value) | Checks whether a value is a date. | ISDATE([Start Date]@row) |
ISERROR | ISERROR(value) | Checks for a calculation error within another formula. | ISERROR([Formula]@row) |
ISEVEN | ISEVEN(number) | Checks whether a number is even. Returns TRUE if even, FALSE if odd. | ISEVEN(4) returns TRUE |
ISNUMBER | ISNUMBER(value) | Checks whether a value is a number. | ISNUMBER([Quantity]@row) |
ISODD | ISODD(number) | Checks whether a number is odd. Returns TRUE if odd, FALSE if even. | ISODD(5) returns TRUE |
ISTEXT | ISTEXT(value) | Checks whether a value is text. | ISTEXT([Name]@row) |
JOIN | JOIN(range, [delimiter]) | Combines a range of cells into a string with optional delimiters between the values. | JOIN([First Name]:[Last Name], " ") |
LARGE | LARGE(range, n) | Returns the n-th highest number in a provided range. | LARGE([Scores]:[Scores], 2) returns the second highest score |
LEFT | LEFT(text, [num_chars]) | Returns the leftmost characters from a text string. | LEFT("Smartsheet", 5) returns “Smart” |
LEN | LEN(text) | Returns the number of characters in a text string, including spaces. | LEN("Hello World") returns 11 |
LOWER | LOWER(text) | Converts any uppercase characters to lowercase. | LOWER("SMARTSHEET") returns “smartsheet” |
MATCH | MATCH(search_value, range, [search_type]) | Returns the relative position of a value in a range. The first position is 1. | MATCH("Complete", [Status]:[Status], 0) |
MAX | MAX(value1, [value2, ...]) | Returns the highest number or latest date. | MAX([Budget], [Actual]) returns the higher value |
MEDIAN | MEDIAN(number1, [number2, ...]) | Returns the value of the midpoint of the range of numbers. | MEDIAN(1, 3, 5) returns 3 |
MID | MID(text, start_position, num_chars) | Returns a portion of text based on a given starting point and number of characters. | MID("Smartsheet", 7, 4) returns “shee” |
MIN | MIN(value1, [value2, ...]) | Returns the lowest number or earliest date. | MIN([Start Date], [End Date]) |
MOD | MOD(dividend, divisor) | Returns the remainder after a division operation. | MOD(10, 3) returns 1 |
MONTH | MONTH(date) | Returns the month number from a date, 1–12. | MONTH("2025-01-17") returns 1 |
MROUND | MROUND(number, [multiple]) | Rounds a number to the nearest specified multiple. | MROUND(10, 3) returns 9 |
NETDAYS | NETDAYS(start_date, end_date) | Returns the number of days from a start date to an end date. | NETDAYS("2025-01-01", "2025-01-10") returns 9 |
NETWORKDAY | NETWORKDAY(start_date, end_date, [holidays]) | Returns the number of working days between two dates, adding 1 if the start date is a non-working day. | NETWORKDAY("2025-01-01", "2025-01-10", [Holiday]) |
NETWORKDAYS | NETWORKDAYS(start_date, end_date, [holidays]) | Returns the number of working days between two dates. | NETWORKDAYS("2025-01-01", "2025-01-10", [Holiday]) |
NOT | NOT(logical_expression) | Provides the opposite of a logical expression within another function. | NOT([Status]@row = "Complete") |
NPV | NPV(discount_rate, number, range1, [range2, ...]) | Calculates the net present value of an investment based on cash flows and a discount rate. | NPV(0.1, [Cash Flow1], [Cash Flow2]) |
OR | OR(logical_expression1, [logical_expression2, ...]) | Returns TRUE if at least one provided logical expression is true. | OR([Status]@row = "Complete", [Priority]@row > 5) |
PARENT | PARENT([reference]) | References the parent of the specified cell. | PARENT([Task ID]) |
PERCENTILE | PERCENTILE(range, percentile) | Returns the value of a given percentile in a range. | PERCENTILE([Scores]:[Scores], 0.9) |
PRORATE | PRORATE(number, start_date, end_date, prorate_start, prorate_end, [decimal_places]) | Returns a portion of a value divided across a provided date range. | PRORATE(1000, "2025-01-01", "2025-12-31", "2025-06-01", "2025-12-31", 2) |
RANKAVG | RANKAVG(number, range, [order]) | Returns the rank of a number relative to others in a range, assigning an average rank to duplicates. | RANKAVG([Score]@row, [Scores]:[Scores], 0) |
RANKEQ | RANKEQ(number, range, [order]) | Returns the rank of a number relative to others in a range. | RANKEQ([Score]@row, [Scores]:[Scores], 0) |
REPLACE | REPLACE(text, start_position, num_chars, new_text) | Replaces a string of characters within text starting at a given position. | REPLACE("Smartsheet", 7, 4, "tools") |
RIGHT | RIGHT(text, [num_chars]) | Returns the rightmost characters from a text string. | RIGHT("Smartsheet", 5) returns “sheet” |
ROUND | ROUND(number, [decimal_places]) | Rounds a number to the desired number of decimal places. | ROUND(3.14159, 2) returns 3.14 |
ROUNDDOWN | ROUNDDOWN(number, [decimal]) | Rounds a number down to a specified number of decimal places. | ROUNDDOWN(3.789, 1) returns 3.7 |
ROUNDUP | ROUNDUP(number, [decimal]) | Rounds a number up to a specified number of decimal places. | ROUNDUP(3.14159, 2) returns 3.15 |
SMALL | SMALL(range, n) | Returns the n-th lowest number in a provided range. | SMALL([Scores]:[Scores], 1) returns the lowest score |
STDEVA | STDEVA(range1, [range2, ...]) | Estimates standard deviation based on a sample set of values. | STDEVA([Scores]:[Scores]) |
STDEVP | STDEVP(range1, [range2, ...]) | Estimates standard deviation based on a set of values, including non-numbers. | STDEVP([Scores]:[Scores]) |
STDEVPA | STDEVPA(range1, [range2, ...]) | Calculates standard deviation based on an entire set of values, including non-numbers. | STDEVPA([Scores]:[Scores]) |
STDEVS | STDEVS(range1, [range2, ...]) | Estimates standard deviation based on a sample population of numbers, ignoring non-numbers. | STDEVS([Scores]:[Scores]) |
SUBSTITUTE | SUBSTITUTE(search_text, old_text, new_text, [replace_num]) | Replaces existing text with new text in a string. | SUBSTITUTE("Hello World", "World", "Smartsheet") |
SUCCESSORS | SUCCESSORS(value) | Calculates the direct successors of a task and returns succeeding task row numbers based on the referenced task. | SUCCESSORS([Task ID]) |
SUM | SUM(number1, [number2, ...]) | Adds a series of numbers. | SUM([Budget]:[Budget]) |
SUMIF | SUMIF(range, criterion, [sum_range]) | Adds numbers within a range that meet a specified condition. | SUMIF([Status]:[Status], "Complete", [Budget]:[Budget]) |
SUMIFS | SUMIFS(range, criterion_range1, criterion1, ...) | Adds numbers within a range when all given conditions in their respective ranges are met. | SUMIFS([Budget]:[Budget], [Status]:[Status], "Complete", [Priority]:[Priority], ">3") |
TIME | TIME(time_value, [format], [precision]) | Creates a time object from a compatible value, displayed in 12 or 24-hour format. | TIME("14:30") returns 2:30 PM |
TODAY | TODAY([number]) | Returns today’s date or evaluates dates to produce outputs like symbols or text values. | TODAY() returns the current date |
TOTALFLOAT | TOTALFLOAT(Value) | Identifies flexibility within your timeline by calculating the time a task can be delayed without impacting the project finish date or the start of a successor task. | TOTALFLOAT([End Date]@row) |
UNICHAR | UNICHAR(number) | Converts a number into a Unicode character. | UNICHAR(65) returns A |
UPPER | UPPER(text) | Converts lowercase characters in a text string to uppercase. | UPPER("smartsheet") returns “SMARTSHEET” |
VALUE | VALUE(text) | Converts a text value that represents a number into a number. | VALUE("100") returns 100 |
VLOOKUP | VLOOKUP(search_value, lookup_table, column_num, [match_type]) | Looks up a value and returns a corresponding value from a different column in the same row. | VLOOKUP("Task A", [Tasks]:[Tasks], 3, FALSE) |
WEEKDAY | WEEKDAY(date) | Returns a number representing the day of the week, 1–7, where Sunday equals 1. | WEEKDAY("2025-01-17") returns 6 (Friday) |
WEEKNUMBER | WEEKNUMBER(date) | Returns a number representing the week of the year, 1–53, where 1 is the first week in the year. | WEEKNUMBER("2025-01-17") returns 3 |
WORKDAY | WORKDAY(date, num_days, [holidays]) | Returns a date from a specified number of working days. | WORKDAY("2025-01-01", 10, [Holiday]) |
YEAR | YEAR(date) | Returns the year from a date as a four-digit number. | YEAR("2025-01-17") returns 2025 |
YEARDAY | YEARDAY(date) | Returns a number representing the day in the year, 1–365, where 1 is the first day of the year. | YEARDAY("2025-01-17") returns 17 |