fbpx

Smartsheet Formula List

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

Complete 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