SVG
To learn more about Smartsheet Regions,click here.
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 hierarchyLearn 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 criteriaLearn 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 significanceLearn more |
CHAR Numeric |
CHAR(
|
Converts a number into a characterLearn 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(
|
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(
|
Counts nonblank valuesLearn 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 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(
|
Combines values for a year, month, and day into a dateLearn more |
DATEONLY Date |
DATEONLY(
|
Use this function 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 dayLearn more |
DECTOHEX Numeric |
DECTOHEX(
|
Converts a decimal number into a hexadecimal value.Learn more |
DESCENDANTS Hierarchy |
DESCENDANTS(
|
Is used within another function to reference all descendant rows of the parent row's referenced cell.Learn more |
DISTINCT Advanced |
DISTINCT(
|
Used within another function to return an array of unique values in a range.Learn more |
FIND Text |
FIND(
|
Use this function to return the starting position of a string within text.Learn more |
FLOOR Numeric |
FLOOR(
|
Rounds a number toward zero to the nearest specified multiple of significanceLearn more |
HAS Logic |
HAS(
|
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(
|
Converts a hexadecimal value to decimal numberLearn 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 and otherwise returns the second valueLearn more |
INDEX Advanced |
INDEX(
|
Returns an item from a collection based on provided row and column indexesLearn more |
INT Numeric |
INT(
|
Returns the integer portion of a numberLearn more |
ISBLANK Logic |
ISBLANK(
|
Checks whether a value is blankLearn more |
ISBOOLEAN Logic |
ISBOOLEAN(
|
检查是否一个布尔值(例如,checkbox, flag, or star)Learn more |
ISCRITICAL Logic
New!
|
ISCRITICAL(
|
Identifies if a row is on the critical path.Learn more |
ISDATE Logic |
ISDATE(
|
Checks whether a value is a dateLearn more |
ISERROR Logic |
ISERROR(
|
Checks for a calculation error within another formulaLearn more |
ISEVEN Logic |
ISEVEN(
|
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(
|
Checks whether a value is a numberLearn more |
ISODD Logic |
ISODD(
|
Checks whether a number is odd. If the value is odd, it returns "True." Otherwise, it returns "False."Learn more |
ISTEXT Logic |
ISTEXT(
|
Checks whether a value is textLearn more |
JOIN Advanced |
JOIN(
|
Combines a range of cells into a string with optional delimiters between the valuesLearn more |
LARGE Numeric |
LARGE(
|
Returns the n-th highest number in a provided rangeLearn more |
LEFT Text |
LEFT(
|
Returns the leftmost characters from a text stringLearn more |
LEN Numeric |
LEN(
|
Returns the number of characters in a text string, including spacesLearn more |
LOWER Text |
LOWER(
|
Converts any uppercase characters to lowercaseLearn 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(
|
回报最高的最新数字或日期。Learn more |
MEDIAN Numeric |
MEDIAN(
|
Returns the value of the midpoint of the range of numbersLearn more |
MID Text |
MID(
|
Returns a portion of text based on a given starting point and number of charactersLearn more |
MIN Numeric |
MIN(
|
Returns the lowest number or earliest dateLearn more |
MOD Numeric |
MOD(
|
Returns the remainder after a division operationLearn more |
MONTH Date |
MONTH(
|
Returns a number representing the number of the month, 1–12, where 1 is JanuaryLearn more |
MROUND Numeric |
MROUND(
|
Returns a number rounded up to the desired multipleLearn more |
NETDAYS Date |
NETDAYS(
|
Returns the number of days from a start date to an end dateLearn 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 expressionLearn more |
NPV Advanced |
NPV(
|
Calculates the net present value of an investment based on a series of cash flows and a discount rateLearn 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 rangeLearn more |
PRORATE Advanced |
PRORATE(
|
Returns a portion of a value divided across a provided date rangeLearn more |
RANKAVG Numeric |
RANKAVG(
|
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(
|
Returns the rank of a number relative to other numbers in a rangeLearn more |
REPLACE Text |
REPLACE(
|
Replaces a string of characters, starting at a given position and ending a given number of characters beyondLearn more |
RIGHT Text |
RIGHT(
|
Returns the rightmost characters from a text stringLearn more |
ROUND Numeric |
ROUND(
|
Rounds a given number to the desired number of decimal placesLearn more |
ROUNDDOWN Numeric |
ROUNDDOWN(
|
Rounds a number down to a specified number of decimal placesLearn more |
ROUNDUP Numeric |
ROUNDUP(
|
Rounds a number up to a specified number of decimal placesLearn more |
SMALL Numeric |
SMALL(
|
Returns the n-th lowest number in a provided rangeLearn more |
STDEVA Advanced |
STDEVA(
|
Estimates standard deviation based on a sample set of valuesLearn more |
STDEVP Advanced |
STDEVP(
|
Estimates standard deviation based on a set of values, including non-numbersLearn more |
STDEVPA Advanced |
STDEVPA(
|
Calculates standard deviation based on an entire set of values, including non-numbersLearn more |
STDEVS Advanced |
STDEVS(
|
Estimates standard deviation based on a sample population of numbers, ignoring non-numbersLearn more |
SUBSTITUTE Text |
SUBSTITUTE(
|
Replaces existing text with new text in a stringLearn more |
SUCCESSORS Numeric |
SUCCESSORS(
|
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(
|
Adds a series of numbersLearn more |
SUMIF Advanced |
SUMIF(
|
Adds numbers within a range that meet a specified conditionLearn more |
SUMIFS Advanced |
SUMIFS(
|
数字范围内当所有给定conditi补充道ons in their respective ranges are met.Learn more |
TODAY Date |
TODAY(
|
Used in a Date column to return the current dateLearn more |
TOTALFLOAT Logic
New!
|
TOTALFLOAT(
|
Calculates the number of days that a task can be delayed without impacting the Project Finish 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 uppercaseLearn more |
VALUE Text |
VALUE(
|
Converts a text value that represents a number into a numberLearn 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 1Learn more |
WEEKNUMBER Date |
WEEKNUMBER(
|
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(
|
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 numberLearn more |
YEARDAY Date |
YEARDAY(
|
Returns a number representing the day in the year, 1–365, where 1 is the first day of the yearLearn more |