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
)
Returns the absolute value of a number. Learn more
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
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
    ]
)
Used within another function to reference the child rows of the referenced parent row. Learn more
COLLECT
Advanced
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
)
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 return an array of unique values in a range. 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 toward zero 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
    ]
)
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 and otherwise returns the second value Learn more
INDEX
Advanced
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
)
Returns the integer portion of a number Learn more
ISBLANK
Logic
ISBLANK(
  • value
)
Checks whether a 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(
  • 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. 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
    ]
)
Returns the relative position of a value in a range (lookup table). The first position is 1. 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
    ]
)
References the parent of 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
    ]
)
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, and 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
    ]
)
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 number(s) 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
TODAY
Date
TODAY(
  • [
    number
    ]
)
Used in a Date column to return the current date Learn more
TOTALFLOAT
Logic
New!
TOTALFLOAT(
  • Value
)
Calculates the number of days that a task can be delayed without impacting the Project Finish Date. 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