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 single value from a range based on the 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]) |
MATCH finds the relative position (not the row number) of a specific value within a list or column. The result of MATCH is a number that represents where the function first encountered the search value or the closest approximate value, depending on the instructions it was given. You can think of it as finding which numbered spot a particular item occupies in a sequence. MATCH has a variety of uses, from determining the relative position of a specific row within a sheet, checking if a value exists in a range at least once, or pairing up with other functions like INDEX to support lookup actions. 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 |