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
New!
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
New!
CEILING(
  • number
  • [
    multiple
    ]
) 		Rounds a number away from zero to the nearest specified multiple of significance. Learn more
CHAR
Numeric
New!
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
New!
CONTAINS(
  • search_for
  • range
) 		Used within another function to search for a character or string. Returns true if found, false if not found. 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
DECTOHEX
Numeric
New!
DECTOHEX(
  • number
) 		Converts a decimal number into a hexadecimal value. Learn more
DESCENDANTS
Hierarchy
New!
DESCENDANTS(
  • [
    parent_cell
    ]
) 		Used within another function to reference all descendant rows of the referenced cell of the parent row. Learn more
DISTINCT
Advanced
New!
DISTINCT(
  • range
) 		Used within another function to reference 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
New!
FLOOR(
  • number
  • [
    multiple
    ]
) 		Rounds a number toward zero to the nearest specified multiple of significance. Learn more
HEXTODEC
Numeric
New!
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, 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
ISEVEN
Logic
New!
ISEVEN(
  • number
) 		Checks whether a number is even. Returns true if even, false if odd. Learn more
ISNUMBER
Logic 		ISNUMBER(
  • value
) 		Checks whether a value is a number. Learn more
ISODD
Logic
New!
ISODD(
  • number
) 		Checks whether a number is odd. Returns true if odd, false if even. 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
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
New!
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
New!
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
New!
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
New!
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
New!
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
New!
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
New!
ROUNDDOWN(
  • number
  • [
    decimal
    ]
) 		Rounds a number down to a specified number of decimal places. Learn more
ROUNDUP
Numeric
New!
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
New!
STDEVA(
  • range1
  • [
    • range2
      • ​
    • ...
    ]
) 		Estimates standard deviation based on a sample set of values. Learn more
STDEVP
Advanced
New!
STDEVP(
  • range1
  • [
    • range2
      • ​
    • ...
    ]
) 		Estimates standard deviation based on a set of values, including non-numbers. Learn more
STDEVPA
Advanced
New!
STDEVPA(
  • range1
  • [
    • range2
      • ​
    • ...
    ]
) 		Calculates standard deviation based on an entire set of values, including non-numbers. Learn more
STDEVS
Advanced
New!
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
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
UNICHAR
Numeric
New!
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-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