SVG
To learn more about Smartsheet Regions, click here.
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 select specific values in a range that meets 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 a value from a range 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 cell 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. Note that this function only works with whole numbers. 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 to calculate the direct successors of a task. This function returns the row numbers of succeeding tasks that occur due to 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 |
| TIME Date |
TIME(time_value, [format], [precision]) |
Returns the time of day in 12- or 24-hour format from a given set of values. Learn more |
| TODAY Date |
TODAY([number]) |
Use TODAY in a formula or a date column to return to today's date, or use it in different column types as part of a formula that evaluates dates and produces other outputs, such as symbols or text values. Learn more |
| TOTALFLOAT Logic New!
|
TOTALFLOAT(Value) |
Use this formula to identify flexibility within your timeline by calculating the time a task can be delayed without impacting the project finish date or the start of a successor task. 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 |