# Functions List

Smartsheet includes common functions found in most desktop spreadsheet programs. You’ll also find a few functions that help with tasks you might do exclusively in Smartsheet (work with data in a hierarchical list, for example). You can use these functions in formulas to manipulate data, to add or subtract dates, or to make calculations—to name just a few things you might want to do.

Here’s a list of all the available functions in Smartsheet.

Title Syntax Description
ABS
Numeric
ABS(
• number
)
ANCESTORS
Hierarchy
ANCESTORS(
• [
reference
]
)
Used within another function to reference all of the ancestors in a cell’s hierarchy. 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
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
• ...
]
)
AVGW
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
)
CHILDREN
Hierarchy
CHILDREN(
• [
reference
]
)
Used within another function to reference the child rows of the referenced parent row. Learn more
COLLECT
COLLECT(
• range
• criterion_range1
• criterion1
• [
• criterion_range2
• criterion2
• ...
]
)
Used within another function to collect specific values in a range that meet the provided criteria. Learn more
CONTAINS
Logic
CONTAINS(
• search_for
• range
)
COUNT
Numeric
COUNT(
• value1
• [
• value2
• ...
]
)
COUNTIF
COUNTIF(
• range
• criterion
)
Counts the number of cells within a range that meet a criterion. Learn more
COUNTIFS
COUNTIFS(
• range1
• criterion1
• [
• range2
• criterion2
• ...
]
)
Counts the number of cells within a range that meet all of the specified criteria. Learn more
COUNTM
New!
COUNTM(
• search_range1
• [
• search_range2
• ...
]
)
Counts the number of elements in a multi-contact or multi-select dropdown column cell or cell range. Returns the total number of elements found. 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
)
Used 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
)
DESCENDANTS
Hierarchy
DESCENDANTS(
• [
parent_cell
]
)
Used within another function to reference all descendant rows of the referenced cell of the parent row. Learn more
DISTINCT
DISTINCT(
• range
)
Used within another function to return an array of unique values in a range. Learn more
FIND
Text
FIND(
• search_for
• text_to_search
• [
start_position
]
)
Returns the starting position (the number of characters in) of a string within text. Learn more
FLOOR
Numeric
FLOOR(
• number
• [
multiple
]
)
Rounds a number toward zero to the nearest specified multiple of significance. Learn more
HAS
Logic
New!
HAS(
• search_range
• criterion
)
Search for an exact match of a value, including multi-contact or multi-select dropdown column cells or ranges. Returns true if found, false if not found. Learn more
HEXTODEC
Numeric
HEXTODEC(
• hex_string
)
IF
Logic
IF(
• logical_expression
• value_if_true
• [
value_if_false
]
)
Evaluates a logical expression and returns one value when true or another when false. Learn more
IFERROR
Logic
IFERROR(
• value
• value_if_error
)
Returns the first value if it isn’t an error, otherwise returns the second value. Learn more
INDEX
INDEX(
• range
• row_index
• [
column_index
]
)
Returns an item from a collection based on provided row and column indexes. Learn more
INT
Numeric
INT(
• value
)
ISBLANK
Logic
ISBLANK(
• value
)
ISBOOLEAN
Logic
ISBOOLEAN(
• value
)
Checks whether a value is a boolean (for example, checkbox, flag, or star). Learn more
ISDATE
Logic
ISDATE(
• value
)
ISERROR
Logic
ISERROR(
• value
)
ISEVEN
Logic
ISEVEN(
• number
)
Checks whether a number is even. Returns true if even, false if odd. Learn more
ISNUMBER
Logic
ISNUMBER(
• value
)
ISODD
Logic
ISODD(
• number
)
Checks whether a number is odd. Returns true if odd, false if even. Learn more
ISTEXT
Logic
ISTEXT(
• value
)
JOIN
JOIN(
• range
• [
delimiter
]
)
Combines a range of cells into a string with optional delimiters between the values. Learn more
LARGE
Numeric
LARGE(
• range
• n
)
LEFT
Text
LEFT(
• text
• [
num_chars
]
)
LEN
Numeric
LEN(
• text
)
Returns the number of characters in a text string, including spaces. Learn more
LOWER
Text
LOWER(
• text
)
MATCH
MATCH(
• search_value
• range
• [
search_type
]
)
Returns the relative position of a value in a range (lookup table). The first position is 1. Learn more
MAX
Numeric
MAX(
• value1
• [
• value2
• ...
]
)
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
• ...
]
)
MOD
Numeric
MOD(
• dividend
• divisor
)
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
]
)
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
]
)
NOT
Logic
NOT(
• logical_expression
)
Used within another function to provide the opposite of a logical expression. Learn more
NPV
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
]
)
PERCENTILE
PERCENTILE(
• range
• percentile
)
Returns the value of a given percentile in a range. Learn more
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. Learn more
RANKAVG
Numeric
RANKAVG(
• number
• range
• [
order
]
)
Returns the rank of a number relative to other numbers in a range. Assigns an average rank to any duplicates. 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
]
)
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
)
STDEVA
STDEVA(
• range1
• [
• range2
• ...
]
)
Estimates standard deviation based on a sample set of values. Learn more
STDEVP
STDEVP(
• range1
• [
• range2
• ...
]
)
Estimates standard deviation based on a set of values, including non-numbers. Learn more
STDEVPA
STDEVPA(
• range1
• [
• range2
• ...
]
)
Calculates standard deviation based on an entire set of values, including non-numbers. Learn more
STDEVS
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
]
)
SUM
Numeric
SUM(
• number1
• [
• number2
• ...
]
)
SUMIF
SUMIF(
• range
• criterion
• [
sum_range
]
)
SUMIFS
SUMIFS(
• range
• criterion_range1
• criterion1
• [
• criterion_range2
• criterion2
• ...
]
)
TODAY
Date
TODAY(
• [
number
]
)
Used in a Date column to return the current date. Learn more
UNICHAR
Numeric
UNICHAR(
• number
)
UPPER
Text
UPPER(
• text
)
VALUE
Text
VALUE(
• text
)
Converts a text value that represents a number into a number. Learn more
VLOOKUP
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-52, 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