SVG
To learn more about Smartsheet Regions, click here.
Functions List
Here’s a list of all the available functions in Smartsheet.
| Title | Syntax | Description |
|---|---|---|
| ABS
Numeric |
ABS(number) |
The ABS function offers a way to ensure that your result is always positive. This function removes a negative state without changing the numeric value for negative results, while not impacting positive values. Learn more |
| ANCESTORS
Hierarchy |
ANCESTORS([reference]) |
Used within another function to reference all direct ancestors in a cell’s hierarchy in a single column. This means the function doesn’t reference all rows above the child row in the hierarchy but only the main parent/grandparent at each level. Learn more |
| AND
Logic |
AND(logical_expression1, [logical_expression2, ...]) |
Used within another function to return true if all provided logical expressions are true. Learn more |
| AVERAGEIF
Advanced |
AVERAGEIF(range, criterion, [average_range]) |
Returns the average of a range of numbers that meet a given criteria Learn more |
| AVG
Numeric |
AVG(number1, [number2, ...]) |
Returns the average (mean) of the provided numbers Learn more |
| AVGW
Advanced |
AVGW(range, range_weight) |
Returns a weighted average (the mean, where each value is weighted by its relative importance) Learn more |
| CEILING
Numeric |
CEILING(number, multiple) |
Rounds a number away from zero to the nearest specified multiple of significance. Learn more |
| CHAR
Numeric |
CHAR(number) |
Converts a number into a character Learn more |
| CHILDREN
Hierarchy |
CHILDREN([reference]) |
Use this function to reference column values of a parent row's child rows. Learn more |
| COLLECT
Advanced |
COLLECT(range, criterion_range1, criterion1, [criterion_range2, criterion2, ...]) |
Used within another function to select specific values in a range that meets the provided criteria Learn more |
| CONTAINS
Logic |
CONTAINS(search_for, range) |
Is used within another function to search for a character or string. It will return "True" if it found the character or string. Otherwise, it will return "False." Learn more |
| COUNT
Numeric |
COUNT(value1, [value2, ...]) |
Counts nonblank values Learn more |
| COUNTIF
Advanced |
COUNTIF(range, criterion) |
Counts the number of cells within a range that meet a criterion. Learn more |
| COUNTIFS
Advanced |
COUNTIFS(range1, criterion1, [range2, criterion2, ...]) |
Counts the number of times all given conditions in their respective ranges are met. Learn more |
| COUNTM
Advanced |
COUNTM(search_range1, [search_range2, ...]) |
Counts the number of elements in a multicontact or multiselect dropdown column cell or cell range. When you use this function, it returns the total number of elements it finds. Learn more |
| DATE
Date |
DATE(year, month, day) |
Combines values for a year, month, and day into a date Learn more |
| DATEONLY
Date |
DATEONLY(date_time) |
Use this function in a Date column to extract the date portion of a date/time value. Learn more |
| DAY
Date |
DAY(date) |
Returns a number representing the day of the month, 1–31, where 1 is the first day Learn more |
| DECTOHEX
Numeric |
DECTOHEX(number) |
Converts a decimal number into a hexadecimal value. Learn more |
| DESCENDANTS
Hierarchy |
DESCENDANTS([parent_cell]) |
Is used within another function to reference all descendant rows of the parent row's referenced cell. Learn more |
| DISTINCT
Advanced |
DISTINCT(range) |
Used within another function to refine a range to unique values by removing duplicates. Learn more |
| FIND
Text |
FIND(search_for, text_to_search, [start_position]) |
Use this function to return the starting position of a string within text. Learn more |
| FLOOR
Numeric |
FLOOR(number, multiple) |
Rounds a number to the nearest specified multiple of significance Learn more |
| HAS
Logic |
HAS(search_range, criterion) |
Searches for an exact match of a single value, including if this value appears in a multicontact or multiselect dropdown column cells or ranges with other values. If HAS finds the exact match, it returns "True." Otherwise, it returns "False." Learn more |
| HEXTODEC
Numeric |
HEXTODEC(hex_string) |
Converts a hexadecimal value to decimal number Learn more |
| IF
Logic |
IF(logical_expression, value_if_true, [value_if_false]) |
Use the IF function to make a choice based on a condition. It’s similar to creating a yes/no question to tell your sheet exactly what value to return based on the answer. Learn more |
| IFERROR
Logic |
IFERROR(value, value_if_error) |
Returns the first value if it isn’t an error and otherwise returns the second value Learn more |
| INDEX
Advanced |
INDEX(range, row_index, [column_index]) |
Returns a value from a range based on provided row and column indexes Learn more |
| INT
Numeric |
INT(value) |
Returns the integer portion of a number Learn more |
| ISBLANK
Logic |
ISBLANK(value) |
Checks whether a cell value is blank Learn more |
| ISBOOLEAN
Logic |
ISBOOLEAN(value) |
Checks whether a value is a boolean (e.g., checkbox, flag, or star) Learn more |
| ISCRITICAL
Logic New!
|
ISCRITICAL(value) |
Identifies if a row is on the critical path Learn more |
| ISDATE
Logic |
ISDATE(value) |
Checks whether a value is a date Learn more |
| ISERROR
Logic |
ISERROR(value) |
Checks for a calculation error within another formula Learn more |
| ISEVEN
Logic |
ISEVEN(number) |
Checks whether a number is even. Note that this function only works with whole numbers. If a value is even, this function returns "True." If the value is odd, this function returns "False." Learn more |
| ISNUMBER
Logic |
ISNUMBER(value) |
Checks whether a value is a number Learn more |
| ISODD
Logic |
ISODD(number) |
Checks whether a number is odd. If the value is odd, it returns "True." Otherwise, it returns "False." Learn more |
| ISTEXT
Logic |
ISTEXT(value) |
Checks whether a value is text Learn more |
| JOIN
Advanced |
JOIN(range, [delimiter]) |
Combines a range of cells into a string with optional delimiters between the values Learn more |
| LARGE
Numeric |
LARGE(range, n) |
Returns the n-th highest number in a provided range Learn more |
| LEFT
Text |
LEFT(text, [num_chars]) |
Returns the leftmost characters from a text string Learn more |
| LEN
Numeric |
LEN(text) |
Returns the number of characters in a text string, including spaces Learn more |
| LOWER
Text |
LOWER(text) |
Converts any uppercase characters to lowercase Learn more |
| MATCH
Advanced |
MATCH(search_value, range, [search_type]) |
The MATCH function helps you locate the exact position of a specific item within a list or table of data in Smartsheet. Think of it like finding out which numbered spot a particular item occupies in a sequence. Learn more |
| MAX
Numeric |
MAX(value1, [value2, ...]) |
Returns the highest number or latest date. Learn more |
| MEDIAN
Numeric |
MEDIAN(number1, [number2, ...]) |
Returns the value of the midpoint of the range of numbers Learn more |
| MID
Text |
MID(text, start_position, num_chars) |
Returns a portion of text based on a given starting point and number of characters Learn more |
| MIN
Numeric |
MIN(value1, [value2, ...]) |
Returns the lowest number or earliest date Learn more |
| MOD
Numeric |
MOD(dividend, divisor) |
Returns the remainder after a division operation Learn more |
| MONTH
Date |
MONTH(date) |
Returns a number representing the number of the month, 1–12, where 1 is January Learn more |
| MROUND
Numeric |
MROUND(number, [multiple]) |
Returns a number rounded up to the desired multiple Learn more |
| NETDAYS
Date |
NETDAYS(start_date, end_date) |
Returns the number of days from a start date to an end date Learn more |
| NETWORKDAY
Date |
NETWORKDAY(start_date, end_date, [holidays]) |
Returns the number of working days between two dates. Adds 1 day to the result if the start date is a non-working day. Learn more |
| NETWORKDAYS
Date |
NETWORKDAYS(start_date, end_date, [holidays]) |
Returns the number of working days between two dates. Learn more |
| NOT
Logic |
NOT(logical_expression) |
Used within another function to provide the opposite of a logical expression Learn more |
| NPV
Advanced |
NPV(discount_rate, number, range1, [range2, ...]) |
Calculates the net present value of an investment based on a series of cash flows and a discount rate Learn more |
| OR
Logic |
OR(logical_expression1, [logical_expression2, ...]) |
Used within another function to return true if at least one provided logical expression is true. Learn more |
| PARENT
Hierarchy |
PARENT([reference]) |
Use this function to find and display the value of the parent for the specified cell. Learn more |
| PERCENTILE
Advanced |
PERCENTILE(range, percentile) |
Returns the value of a given percentile in a range Learn more |
| PRORATE
Advanced |
PRORATE(number, start_date, end_date, prorate_start, prorate_end, [decimal_places]) |
The PRORATE function in Smartsheet allows you to calculate a fair share of a total value based on a specific, shorter period of time within a larger date range. Learn more |
| RANKAVG
Numeric |
RANKAVG(number, range, [order]) |
The RANKAVG function helps you understand how a specific number compares to a list of other numbers by assigning it a rank. Think of it like ranking contestants in a competition: The best performer gets rank 1, the next best gets rank 2, and so on. Learn more |
| RANKEQ
Numeric |
RANKEQ(number, range, [order]) |
Returns the rank of a number relative to other numbers in a range Learn more |
| REPLACE
Text |
REPLACE(text, start_position, num_chars, new_text) |
Replaces a string of characters, starting at a given position and ending a given number of characters beyond Learn more |
| RIGHT
Text |
RIGHT(text, [num_chars]) |
Returns the rightmost characters from a text string Learn more |
| ROUND
Numeric |
ROUND(number, [decimal_places]) |
Rounds a given number to the desired number of decimal places Learn more |
| ROUNDDOWN
Numeric |
ROUNDDOWN(number, [decimal]) |
Rounds a number down to a specified number of decimal places Learn more |
| ROUNDUP
Numeric |
ROUNDUP(number, [decimal]) |
Rounds a number up to a specified number of decimal places Learn more |
| SMALL
Numeric |
SMALL(range, n) |
Returns the n-th lowest number in a provided range Learn more |
| STDEVA
Advanced |
STDEVA(range1, [range2, ...]) |
Estimates standard deviation based on a sample set of values Learn more |
| STDEVP
Advanced |
STDEVP(range1, [range2, ...]) |
Estimates standard deviation based on a set of values, including non-numbers Learn more |
| STDEVPA
Advanced |
STDEVPA(range1, [range2, ...]) |
Calculates standard deviation based on an entire set of values, including non-numbers Learn more |
| STDEVS
Advanced |
STDEVS(range1, [range2, ...]) |
Estimates standard deviation based on a sample population of numbers, ignoring non-numbers Learn more |
| SUBSTITUTE
Text |
SUBSTITUTE(search_text, old_text, new_text, [replace_num]) |
Replaces existing text with new text in a string Learn more |
| SUCCESSORS
Numeric |
SUCCESSORS(value) |
Used within another function to calculate the direct successors of a task and return succeeding task row numbers that occur as a result of the referenced task. Learn more |
| SUM
Numeric |
SUM(number1, [number2, ...]) |
Adds a series of numbers Learn more |
| SUMIF
Advanced |
SUMIF(range, criterion, [sum_range]) |
Adds numbers within a range that meet a specified condition Learn more |
| SUMIFS
Advanced |
SUMIFS(range, criterion_range1, criterion1, [criterion_range2, criterion2, ...]) |
Adds numbers within a range when all given conditions in their respective ranges are met. Learn more |
| TIME
Date |
TIME(time_value, [format], [precision]) |
Create a time object from a compatible value which can be displayed in a 12 or 24-hour format. Learn more |
| TODAY
Date |
TODAY([number]) |
Use TODAY in a formula or a date column to return today's date, or use it in other column types as part of a formula that evaluates dates and produces other output types, such as symbols or text values. Learn more |
| TOTALFLOAT
Logic New!
|
TOTALFLOAT(Value) |
Use this formula to identify 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. Learn more |
| UNICHAR
Numeric |
UNICHAR(number) |
Converts a number into a Unicode character. Learn more |
| UPPER
Text |
UPPER(text) |
Converts lowercase characters in a text string to uppercase Learn more |
| VALUE
Text |
VALUE(text) |
Converts a text value that represents a number into a number Learn more |
| VLOOKUP
Advanced |
VLOOKUP(search_value, lookup_table, column_num, [match_type]) |
Looks up a value and returns a corresponding value in the same row but from a different column. Learn more |
| WEEKDAY
Date |
WEEKDAY(date) |
Returns a number representing the day of the week, 1–7, where Sunday equals 1 Learn more |
| WEEKNUMBER
Date |
WEEKNUMBER(date) |
Returns a number representing the week of the year, 1–53, where 1 is the first week in the year. Learn more |
| WORKDAY
Date |
WORKDAY(date, num_days, [holidays]) |
Used in a Date column to return a date from a specified number of working days. Learn more |
| YEAR
Date |
YEAR(date) |
Returns the year from a date as a four-digit number Learn more |
| YEARDAY
Date |
YEARDAY(date) |
Returns a number representing the day in the year, 1–365, where 1 is the first day of the year Learn more |