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 hierarchyLearn 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 criteriaLearn 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 significanceLearn more
CHAR
Numeric
CHAR(
  • number
)
Converts a number into a characterLearn 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 valuesLearn 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 dateLearn 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 dayLearn 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 significanceLearn 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 numberLearn 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 valueLearn more
INDEX
Advanced
INDEX(
  • range
  • row_index
  • [
    column_index
    ]
)
Returns an item from a collection based on provided row and column indexesLearn more
INT
Numeric
INT(
  • value
)
Returns the integer portion of a numberLearn more
ISBLANK
Logic
ISBLANK(
  • value
)
Checks whether a value is blankLearn more
ISBOOLEAN
Logic
ISBOOLEAN(
  • value
)
检查是否一个布尔值(例如,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 dateLearn more
ISERROR
Logic
ISERROR(
  • value
)
Checks for a calculation error within another formulaLearn 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 numberLearn 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 textLearn more
JOIN
Advanced
JOIN(
  • range
  • [
    delimiter
    ]
)
Combines a range of cells into a string with optional delimiters between the valuesLearn more
LARGE
Numeric
LARGE(
  • range
  • n
)
Returns the n-th highest number in a provided rangeLearn more
LEFT
Text
LEFT(
  • text
  • [
    num_chars
    ]
)
Returns the leftmost characters from a text stringLearn more
LEN
Numeric
LEN(
  • text
)
Returns the number of characters in a text string, including spacesLearn more
LOWER
Text
LOWER(
  • text
)
Converts any uppercase characters to lowercaseLearn 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
    • ...
    ]
)
回报最高的最新数字或日期。Learn more
MEDIAN
Numeric
MEDIAN(
  • number1
  • [
    • number2
    • ...
    ]
)
Returns the value of the midpoint of the range of numbersLearn more
MID
Text
MID(
  • text
  • start_position
  • num_chars
)
Returns a portion of text based on a given starting point and number of charactersLearn more
MIN
Numeric
MIN(
  • value1
  • [
    • value2
    • ...
    ]
)
Returns the lowest number or earliest dateLearn more
MOD
Numeric
MOD(
  • dividend
  • divisor
)
Returns the remainder after a division operationLearn more
MONTH
Date
MONTH(
  • date
)
Returns a number representing the number of the month, 1–12, where 1 is JanuaryLearn more
MROUND
Numeric
MROUND(
  • number
  • [
    multiple
    ]
)
Returns a number rounded up to the desired multipleLearn more
NETDAYS
Date
NETDAYS(
  • start_date
  • end_date
)
Returns the number of days from a start date to an end dateLearn 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 expressionLearn 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 rateLearn 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 rangeLearn 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 rangeLearn 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 rangeLearn 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 beyondLearn more
RIGHT
Text
RIGHT(
  • text
  • [
    num_chars
    ]
)
Returns the rightmost characters from a text stringLearn more
ROUND
Numeric
ROUND(
  • number
  • [
    decimal_places
    ]
)
Rounds a given number to the desired number of decimal placesLearn more
ROUNDDOWN
Numeric
ROUNDDOWN(
  • number
  • [
    decimal
    ]
)
Rounds a number down to a specified number of decimal placesLearn more
ROUNDUP
Numeric
ROUNDUP(
  • number
  • [
    decimal
    ]
)
Rounds a number up to a specified number of decimal placesLearn more
SMALL
Numeric
SMALL(
  • range
  • n
)
Returns the n-th lowest number in a provided rangeLearn more
STDEVA
Advanced
STDEVA(
  • range1
  • [
    • range2
    • ...
    ]
)
Estimates standard deviation based on a sample set of valuesLearn more
STDEVP
Advanced
STDEVP(
  • range1
  • [
    • range2
    • ...
    ]
)
Estimates standard deviation based on a set of values, including non-numbersLearn more
STDEVPA
Advanced
STDEVPA(
  • range1
  • [
    • range2
    • ...
    ]
)
Calculates standard deviation based on an entire set of values, including non-numbersLearn more
STDEVS
Advanced
STDEVS(
  • range1
  • [
    • range2
    • ...
    ]
)
Estimates standard deviation based on a sample population of numbers, ignoring non-numbersLearn more
SUBSTITUTE
Text
SUBSTITUTE(
  • search_text
  • old_text
  • new_text
  • [
    replace_num
    ]
)
Replaces existing text with new text in a stringLearn 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 numbersLearn more
SUMIF
Advanced
SUMIF(
  • range
  • criterion
  • [
    sum_range
    ]
)
Adds numbers within a range that meet a specified conditionLearn more
SUMIFS
Advanced
SUMIFS(
  • range
  • criterion_range1
  • criterion1
  • [
    • criterion_range2
    • criterion2
    • ...
    ]
)
数字范围内当所有给定conditi补充道ons in their respective ranges are met.Learn more
TODAY
Date
TODAY(
  • [
    number
    ]
)
Used in a Date column to return the current dateLearn 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 uppercaseLearn more
VALUE
Text
VALUE(
  • text
)
Converts a text value that represents a number into a numberLearn 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 1Learn 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 numberLearn more
YEARDAY
Date
YEARDAY(
  • date
)
Returns a number representing the day in the year, 1–365, where 1 is the first day of the yearLearn more