SVG
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(
|
Returns the absolute value of a number. Learn more |
ANCESTORS
Hierarchy |
ANCESTORS(
|
Used within another function to reference all of the ancestors in a cell’s hierarchy. Learn more |
AND
Logic |
AND(
|
Used within another function to return true if all provided logical expressions are true. Learn more |
AVERAGEIF
Advanced |
AVERAGEIF(
|
Returns the average of a range of numbers that meet a given criteria. Learn more |
AVG
Numeric |
AVG(
|
Returns the average (mean) of the provided numbers. Learn more |
AVGW
Advanced |
AVGW(
|
Returns a weighted average (the mean, where each value is weighted by its relative importance). Learn more |
CEILING
Numeric |
CEILING(
|
Rounds a number away from zero to the nearest specified multiple of significance. Learn more |
CHAR
Numeric |
CHAR(
|
Converts a number into a character. Learn more |
CHILDREN
Hierarchy |
CHILDREN(
|
Used within another function to reference the child rows of the referenced parent row. Learn more |
COLLECT
Advanced |
COLLECT(
|
Used within another function to collect specific values in a range that meet the provided criteria. Learn more |
CONTAINS
Logic |
CONTAINS(
|
Used within another function to search for a character or string. Returns true if found, false if not found. Learn more |
COUNT
Numeric |
COUNT(
|
Counts non-blank values. Learn more |
COUNTIF
Advanced |
COUNTIF(
|
Counts the number of cells within a range that meet a criterion. Learn more |
COUNTIFS
Advanced |
COUNTIFS(
|
Counts the number of times all given conditions in their respective ranges are met. Learn more |
COUNTM
Advanced |
COUNTM(
|
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(
|
Combines values for a year, month, and day into a date. Learn more |
DATEONLY
Date |
DATEONLY(
|
Used in a Date column to extract the date portion of a date/time value. Learn more |
DAY
Date |
DAY(
|
Returns a number representing the day of the month, 1-31, where 1 is the first day. Learn more |
DECTOHEX
Numeric |
DECTOHEX(
|
Converts a decimal number into a hexadecimal value. Learn more |
DESCENDANTS
Hierarchy |
DESCENDANTS(
|
Used within another function to reference all descendant rows of the referenced cell of the parent row. Learn more |
DISTINCT
Advanced |
DISTINCT(
|
Used within another function to return an array of unique values in a range. Learn more |
FIND
Text |
FIND(
|
Returns the starting position (the number of characters in) of a string within text. Learn more |
FLOOR
Numeric |
FLOOR(
|
Rounds a number toward zero to the nearest specified multiple of significance. Learn more |
HAS
Logic |
HAS(
|
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(
|
Converts a hexadecimal value to decimal number. Learn more |
IF
Logic |
IF(
|
Evaluates a logical expression and returns one value when true or another when false. Learn more |
IFERROR
Logic |
IFERROR(
|
Returns the first value if it isn’t an error, otherwise returns the second value. Learn more |
INDEX
Advanced |
INDEX(
|
Returns an item from a collection based on provided row and column indexes. Learn more |
INT
Numeric |
INT(
|
Returns the integer portion of a number. Learn more |
ISBLANK
Logic |
ISBLANK(
|
Checks whether a value is blank. Learn more |
ISBOOLEAN
Logic |
ISBOOLEAN(
|
Checks whether a value is a boolean (for example, checkbox, flag, or star). Learn more |
ISDATE
Logic |
ISDATE(
|
Checks whether a value is a date. Learn more |
ISERROR
Logic |
ISERROR(
|
Checks for a calculation error within another formula. Learn more |
ISEVEN
Logic |
ISEVEN(
|
Checks whether a number is even. Returns true if even, false if odd. Learn more |
ISNUMBER
Logic |
ISNUMBER(
|
Checks whether a value is a number. Learn more |
ISODD
Logic |
ISODD(
|
Checks whether a number is odd. Returns true if odd, false if even. Learn more |
ISTEXT
Logic |
ISTEXT(
|
Checks whether a value is text. Learn more |
JOIN
Advanced |
JOIN(
|
Combines a range of cells into a string with optional delimiters between the values. Learn more |
LARGE
Numeric |
LARGE(
|
Returns the n-th highest number in a provided range. Learn more |
LEFT
Text |
LEFT(
|
Returns the leftmost characters from a text string. Learn more |
LEN
Numeric |
LEN(
|
Returns the number of characters in a text string, including spaces. Learn more |
LOWER
Text |
LOWER(
|
Converts any uppercase characters to lowercase. Learn more |
MATCH
Advanced |
MATCH(
|
Returns the relative position of a value in a range (lookup table). The first position is 1. Learn more |
MAX
Numeric |
MAX(
|
Returns the highest number or latest date. Learn more |
MEDIAN
Numeric New!
|
MEDIAN(
|
Returns the value of the midpoint of the range of numbers. Learn more |
MID
Text |
MID(
|
Returns a portion of text based on a given starting point and number of characters. Learn more |
MIN
Numeric |
MIN(
|
Returns the lowest number or earliest date. Learn more |
MOD
Numeric |
MOD(
|
Returns the remainder after a division operation. Learn more |
MONTH
Date |
MONTH(
|
Returns a number representing the number of the month, 1-12, where 1 is January. Learn more |
MROUND
Numeric |
MROUND(
|
Returns a number rounded up to the desired multiple. Learn more |
NETDAYS
Date |
NETDAYS(
|
Returns the number of days from a start date to an end date. Learn more |
NETWORKDAY
Date |
NETWORKDAY(
|
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(
|
Returns the number of working days between two dates. Learn more |
NOT
Logic |
NOT(
|
Used within another function to provide the opposite of a logical expression. Learn more |
NPV
Advanced |
NPV(
|
Calculates the net present value of an investment based on a series of cash flows and a discount rate. Learn more |
OR
Logic |
OR(
|
Used within another function to return true if at least one provided logical expression is true. Learn more |
PARENT
Hierarchy |
PARENT(
|
References the parent of the specified cell. Learn more |
PERCENTILE
Advanced |
PERCENTILE(
|
Returns the value of a given percentile in a range. Learn more |
PRORATE
Advanced |
PRORATE(
|
Returns a portion of a value divided across a provided date range. Learn more |
RANKAVG
Numeric |
RANKAVG(
|
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(
|
Returns the rank of a number relative to other numbers in a range. Learn more |
REPLACE
Text |
REPLACE(
|
Replaces a string of characters, starting at a given position and ending a given number of characters beyond. Learn more |
RIGHT
Text |
RIGHT(
|
Returns the rightmost characters from a text string. Learn more |
ROUND
Numeric |
ROUND(
|
Rounds a given number to the desired number of decimal places. Learn more |
ROUNDDOWN
Numeric |
ROUNDDOWN(
|
Rounds a number down to a specified number of decimal places. Learn more |
ROUNDUP
Numeric |
ROUNDUP(
|
Rounds a number up to a specified number of decimal places. Learn more |
SMALL
Numeric |
SMALL(
|
Returns the n-th lowest number in a provided range. Learn more |
STDEVA
Advanced |
STDEVA(
|
Estimates standard deviation based on a sample set of values. Learn more |
STDEVP
Advanced |
STDEVP(
|
Estimates standard deviation based on a set of values, including non-numbers. Learn more |
STDEVPA
Advanced |
STDEVPA(
|
Calculates standard deviation based on an entire set of values, including non-numbers. Learn more |
STDEVS
Advanced |
STDEVS(
|
Estimates standard deviation based on a sample population of numbers, ignoring non-numbers. Learn more |
SUBSTITUTE
Text |
SUBSTITUTE(
|
Replaces existing text with new text in a string. Learn more |
SUM
Numeric |
SUM(
|
Adds a series of numbers. Learn more |
SUMIF
Advanced |
SUMIF(
|
Adds numbers within a range that meet a specified condition. Learn more |
SUMIFS
Advanced |
SUMIFS(
|
Adds numbers within a range when all given conditions in their respective ranges are met. Learn more |
TODAY
Date |
TODAY(
|
Used in a Date column to return the current date. Learn more |
UNICHAR
Numeric |
UNICHAR(
|
Converts a number into a Unicode character. Learn more |
UPPER
Text |
UPPER(
|
Converts lowercase characters in a text string to uppercase. Learn more |
VALUE
Text |
VALUE(
|
Converts a text value that represents a number into a number. Learn more |
VLOOKUP
Advanced |
VLOOKUP(
|
Looks up a value and returns a corresponding value in the same row but from a different column. Learn more |
WEEKDAY
Date |
WEEKDAY(
|
Returns a number representing the day of the week, 1-7, where Sunday equals 1. Learn more |
WEEKNUMBER
Date |
WEEKNUMBER(
|
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(
|
Used in a Date column to return a date from a specified number of working days. Learn more |
YEAR
Date |
YEAR(
|
Returns the year from a date as a four-digit number. Learn more |
YEARDAY
Date |
YEARDAY(
|
Returns a number representing the day in the year, 1-365, where 1 is the first day of the year. Learn more |