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
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
CHILDREN
Hierarchy
CHILDREN(
  • [
    reference
    ]
)
Used within another function to reference the child rows of the referenced parent row. Learn more
COUNT
Numeric
COUNT(
  • value1
  • [
    • value2
    • ...
    ]
)
Counts non-blank 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 cells within a range that meet all of the specified criteria. 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
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
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
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 (for example, checkbox, flag, or star). 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
ISNUMBER
Logic
ISNUMBER(
  • value
)
Checks whether a value is a number. 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
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
LOOKUP
Advanced
LOOKUP(
  • 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
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
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
MONTH
Date
MONTH(
  • date
)
Returns a number representing the number of the month, 1-12, where 1 is January. Learn more
NETDAYS
Date
NETDAYS(
  • start_date
  • end_date
)
Returns the number of days between two dates. 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
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
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
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
SUBSTITUTE
Text
SUBSTITUTE(
  • search_text
  • old_text
  • new_text
  • [
    replace_num
    ]
)
Replaces existing text with new text in a string. 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 that meet multiple specified criteria. Learn more
TODAY
Date
TODAY(
  • [
    number
    ]
)
Used in a Date column to return the current date. 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
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