Functions List for Smartsheet Formulas

Add functions to your formulas in Smartsheet to perform calculations on your sheet data and to automate sheet actions. You can use functions in formulas, for example, to sum a range of expenses, or to check a box when a specific value enters a cell. 

Additional Resources

This article provides information on the functions available to Smartsheet. Even if you've created formulas in a different application, such as Excel, formula syntax in Smartsheet may be different:

Work with Examples Right in Smartsheet

To see detailed examples—including syntax and parameter requirements—and to practice using formulas, try the Smartsheet Formula Examples template.

The examples in this article refer to the data from the following image.

Sample Data


Available Functions by Type

Numeric Functions

Perform mathematical calculations, including sum and average.

ABS, AVG, COUNT, INT, LEN, MAX, MIN, ROUND, SUM

Logic Functions

Test for conditions that you specify and return a value of true or false.

AND, IF, ISBLANK, ISBOOLEAN, IFERROR, ISDATE, ISERROR, ISNUMBER, ISTEXT, NOT, OR

Text Functions

Work with text strings.

FIND, LEFT, LOWER, MID, REPLACE, RIGHT, SUBSTITUTE, UPPER, VALUE

Date Functions

Manipulate and display dates and times, calculate working days, and so on.

DATE, DATEONLY, DAY, MONTH, NETDAYS, NETWORKDAY, NETWORKDAYS, TODAY, WEEKDAY, WEEKNUMBER, WORKDAY, YEAR, YEARDAY

Advanced Functions

Count or sum values that meet specific criteria, calculate weighted averages, prorate.

AVGW, COUNTIF, COUNTIFS, INDEX, JOIN, LOOKUPMATCH, PRORATE, SUMIF, SUMIFS

Hierarchical Functions

ANCESTORS, CHILDREN, PARENT

Using @cell in Formulas

@cell


Available Functions

Listed below (in alphabetical order) are all available functions in Smartsheet.

ABS (Numeric Function)

Description

Returns the absolute value of a given number.

Syntax

ABS(numeric_value)

The ABS function takes the following argument:

  • numeric_value—A referenced cell that contains a numeric value or a manually typed number.

Example

=ABS(-85)

Result: 85


ANCESTORS (Advanced Function)

Description

Returns all of the ancestors in the hierarchy of the referenced cell. Must be used inside another function such as COUNT, INDEX, or JOIN.

Syntax

ANCESTORS(cell_reference)

The ANCESTORS function takes the following argument:

  • cell_reference (optional) — A specific cell. (If no cell is specified, the function references the ancestors of the current cell.)

Example

=COUNT(ANCESTORS([Task Name]4))

Result: 1


AND (Logic Function)

Description

Evaluates if a set of logical expressions are true or false. If any expression is false it will evaluate as false. This function can be used directly in a boolean column (checkbox, flag, star) or can be used within another function, such as IF, SUM, SUMIF, COUNT, or COUNTIF.

Syntax

AND(boolean_expression1, boolean_expression2, …)

The AND function takes the following arguments:

  • boolean_expression1—The logical expression that you want to evaluate (this can be a formula or a reference to a cell containing a formula).
  • boolean_expression2 (optional)—Additional logical expressions you want to evaluate (these can be formulas or references to cells containing a formula).

Example

=IF(AND(Done1 = 1, Done2 = 1, Done3 = 1), "All Tasks Complete", "Tasks Incomplete")

Result: Tasks Incomplete


AVG (Numeric Function)

Description

Averages selected values or a collection of cells.

Syntax

AVG(numeric_collection)

The AVG function takes the following argument:

  • numeric_collection—Can be a range or specific cells separated by commas.

Example

=AVG(Cost1:Cost5)

Result: 225.15


AVGW (Advanced Function)

Description

Calculates an average resulting from the multiplication of values by a corresponding weighted factor which reflects their importance.

Syntax

AVGW(numeric_range, weight_range)

The AVGW function takes the following arguments:

  • numeric_range—The group of cells containing numeric values.
  • weight_range—Corresponding group of numeric values that represents the number of times you want the value counted.

Examples

See the Smartsheet Formula Examples Sheet for more details.


CHILDREN (Advanced Function)

Description

Returns the child rows of the referenced parent row. This function must be used within another function, such as IF, SUM, SUMIF, COUNT, or COUNTIF.

Syntax

CHILDREN(cell_reference)

The CHILDREN function takes the following argument:

  • cell_reference (optional)—A specific parent cell. (If no cell is specified, the function references the children of the current row.)

Example

=COUNT(CHILDREN([Task Name]1))

Result: 3

=SUM(CHILDREN(Cost1))

Result: $450.00


COUNT (Numeric Function)

Description

Counts non-blank cells in a given collection.

Syntax

COUNT(collection)

The COUNT function takes the following argument:

  • collection—The group of cells that you want to count. This can be a range or individual cell references, separated by commas.

Example

=COUNT([Task Name]1:[Task Name]5)

Result: 5

=COUNT([Task Name]:[Task Name])

Result: 10


COUNTIF (Advanced Function)

Description

Counts the number of cells within a range that meet a criterion.

Syntax

COUNTIF(range, value)

The COUNTIF function takes the following arguments:

  • range—The group of cells that you want to count.
  • value—A value or reference to a cell containing a value.

Example

=COUNTIF(Done:Done, 1)

Result: 2

=COUNTIF(Cost1:Cost5, > 200)

Result: 2


COUNTIFS (Advanced Function)

Description

Counts the number of cells within a range that meet the specified criteria.

Syntax

COUNTIFS(range1, value1, range2, value2, ...)

The COUNTIFS function takes the following arguments:

  • range1—Collection of cells that you want to count.
  • value1—A value or reference to a cell containing a value.
  • range2 (optional)—Additional collections of cells you want to count.
  • value2 (optional)—Additional value or reference to a cell containing a value.

Example

=COUNTIFS(Done:Done, 1, [Task Name]:[Task Name], "Task A")

Result: 1


DATE (Date Function)

Description

Creates a date. Typically used when a formula needs to compute a date as an argument for another function.

Syntax

DATE(year, month, day)

The DATE function takes the following arguments:

  • year—A manually typed numeric value or cell referencing a numeric value that represents the year. If manually typed, the year must be expressed in YYYY format.
  • month—A numeric value or cell referencing a numeric value that represents the month in MM format.
  • day—A numeric value or cell referencing a numeric value that represents the day in DD format.

Example

=DATE(2013, 10, 14)

Result: 10/14/13 (US date format)


DATEONLY (Date Function)

Description

Extracts the date portion of a date/time value.

Syntax

DATEONLY(date_time_value)

The DATEONLY function takes the following argument:

  • date_time_value—The value or reference to a Date type or System cell containing the date_time_value.

Example

=DATEONLY([Due Date]5)

Result: 03/01/14

Notes


DAY (Date Function)

Description

Returns an integer from a date value as day of the month 1-31, in numeric format.

Syntax

DAY(date_value)

The DAY function takes the following argument:

  • date_value —The referenced cell (must be of a Date column type) or other date value.

Example

=DAY([Due Date]5)

Result: 1


FIND (Text Function)

Description

Returns the starting position (the number of characters in) of a specified string.

Syntax

FIND(find_value, value)

The FIND function takes the following arguments:

  • find_value—The value or reference to a cell containing the value that you want to find.
  • value—A value or reference to a cell containing the text or value that contains the sequence of characters or numbers that you want to find.

Example

=FIND("D", [Task Name]4)

Result: 6


IF (Logic Function)

Description

Performs a logical evaluation and returns a value if the expression evaluates to true. You can also specify that a different value be returned if the expression evaluates to false.

Syntax

IF(logical_expression1, return_if_true, return_if_false)

IF(logical_expression1, return_if_true, IF(logical_expression2, return_if_true2, return_if_false))

The IF function takes the following arguments:

  • logical_expression—Expression you want to evaluate. You can use values, reference cells containing values, or use a combination of values and cell references in the expression.
  • return_if_true—A value, function (such as COUNT or SUM), or reference to a cell containing a value or function. This will be returned or calculated if the logical expression is true.
  • return_if_false (optional)—A value, function, or reference to a cell containing a value or function. This will be returned or calculated if the expression is false.
  • logical_expression2 (optional)—Additional expression you want to evaluate.
  • return_if_true2 (optional)—Additional value, function, or reference to a cell containing a value or function. This will be returned if the second logical expression is true, and other logical expressions to the left of the formula are false.

Example

=IF([Due Date]1 > [Due Date]2, "Date 1 is Larger", "Date 2 is Larger")

Result: Date 2 is Larger

=IF([Task Name]1 = "Task A", "This is Task A", IF([Task Name]1 = "Task B", "This is Task B", "Neither Task A nor Task B"))

Result: This is Task A

Notes

  • You can nest IF functions to perform multiple logical evaluations (see the Syntax and Example sections above). Smartsheet reads the IF statements in the formula from left to right, displaying a value based on the first expression to evaluate to true.
  • When nesting IF functions, the optional return_if_false will be returned only when all logical expressions evaluate to false.

IFERROR (Logic Function)

Description

Used to suppress calculation errors in a sheet such as #DIVIDE BY ZERO.

Syntax

IFERROR(logical_expression, return_if_error)

The IFERROR function takes the following arguments:

  • logical_expression—Formula, manually typed value, or reference to a cell (typically containing a formula) that you want to test for an error.
  • return_if_error (optional)—A value, function (such as COUNT or SUM), or reference to a cell containing a value or function.

Example

=IFERROR([Task Name]15, "Data needed")

This example and result assumes that [Task Name]15 results in a calculation error such as #DIVIDE BY ZERO.

Result: Data needed

Notes

  • This function does not suppress errors caused by improperly constructed formulas.
  • This is similar to nesting the ISERROR function within an IF function, but with IFERROR you don't have to retype or re-evaluate the expression.
  • If no error is found, and the logical expression either a manually typed value or reference to a cell containing a value, that value will be returned instead.

INDEX (Advanced Function)

Description

Returns an item from a collection based on an index position that you specify.

Syntax

INDEX(collection, index_number_1, index_number_2)

The INDEX function takes the following arguments:

  • collection —The group of cells you want the index of; this can be a range of cells or it can be a function that returns a collection such as ANCESTORS or CHILDREN.
  • index_number_1—The row position (used in a one-dimensional collection such as a list)
  • index_number_2 (optional)—The column position (used in a two-dimensional collection such as a table)

Example

=INDEX([Task Name]1:[Due Date]5, 1, 2)

Result: 100 (the value that appears in the first row, second column of the collection)


INT (Numeric Function)

Description

Returns the integer portion of a given number.

Syntax

INT(value)

The INT function takes the following argument:

  • value—A value or reference to a cell containing a value.

Example

=INT(Cost5)

Result: 425

Notes

  • In Smartsheet, the INT function rounds toward 0 in its calculation. This behavior differs from Excel and Google Sheets, which round the result down in their calculations. For example, the formula =INT(-1.6) returns -1 as a result in Smartsheet, whereas it returns -2 as a result in Excel and Google Sheets.

ISBLANK (Logic Function)

Description

Checks to see if a value is blank. Must be used within other functions, such as IF, COUNTIF, or SUMIF.

Syntax

ISBLANK(value)

The ISBLANK function takes the following argument:

  • value—A value or reference to a cell containing a value.

Example

=IF(ISBLANK([Task Name]1), "Cell is blank", "Cell isn't blank")

Result: Cell isn't blank


ISBOOLEAN (Logic Function)

Description

Checks to see if a value is boolean (such as a checkbox, flag, or star). Must be used within other functions, such as IF, COUNTIF, or SUMIF.

Syntax

ISBOOLEAN(value)

The ISBOOLEAN takes the following argument:

  • value—A value or reference to a cell containing a value.

Example

=IF(ISBOOLEAN(Done1), "Cell contains a boolean value", "Cell doesn’t contain a boolean value")

Result: Cell contains a boolean value


ISDATE (Logic Function)

Description

Checks to see if a value is a date. Must be used within other functions, such as IF, COUNTIF, or SUMIF.

Syntax

ISDATE(value)

The ISDATE function takes the following argument:

  • value—A value or reference to a cell containing a value.

Example

=IF(ISDATE([Due Date]1), "Cell contains a date", "Cell doesn’t contain a date")

Result: Cell contains a date


ISERROR (Logic Function)

Description

Checks for a calculation error such as #DIVIDE BY ZERO. Must be used within other functions, such as IF, COUNTIF, or SUMIF.

Syntax

ISERROR(logical_expression)

The ISERROR function takes the following argument:

  • logical_expression—The formula that you want to check for an error. Can be contained within the ISERROR function or a reference to a cell containing a formula.

Example

This example and result assumes that [Task Name]15 results in a calculation error such as #DIVIDE BY ZERO.

=IF(ISERROR([Task Name]15), "There's a calculation error")

Result: There’s a calculation error.


ISNUMBER (Logic Function)

Description

Checks to see if a value is a number. Must be used within other functions, such as IF, COUNTIF, or SUMIF.

Syntax

ISNUMBER(value)

The ISNUMBER takes the following argument:

  • value—Manually expressed value in the formula or reference to a cell.

Example

=IF(ISNUMBER([Task Name]1), "Cell is a number", "Cell isn't a number")

Result: Cell isn't a number


ISTEXT (Logic Function)

Description

Checks to see if a value is text. Must be used within other functions, such as IF, COUNTIF, or SUMIF.

Syntax

ISTEXT(value)

The ISTEXT takes the following argument:

  • value—The value in the formula or reference to a cell that you want to check.

Example

=IF(ISTEXT([Due Date]1), "Value is text", "Value isn't text")

Result: Value isn't text


JOIN (Advanced Function)

Description

Expands a collection into a string with optional delimiters between the values.

Syntax

JOIN(range, delimiters)

The JOIN function takes the following arguments:

  • range —The range of cells from which you want to return values.
  • delimiters (optional)—A value used to separate data in the range, such as a hyphen, comma, or space. Can be a formula, a manually entered value, or a reference to a cell.

Example

=JOIN([Task Name]2:[Task Name]6, " - ")

Result: Task A - Task B - Task C - Task D - Task E

Notes

  • This is a good alternative to using + (the plus sign) to concatenate text strings.

LEFT (Text Function)

Description

Returns the number of characters stated, starting from the leftmost character in the string.

Syntax

LEFT(text_value, number_of_characters)

The LEFT function takes the following arguments:

  • text_value—A text value or reference to a cell containing a text value.
  • number_of_characters (optional)—Starting from the leftmost character, the number of characters that you want returned from the referenced cell.

=LEFT([Task Name]5, 3)

Result: Tas


LEN (Numeric Function)

Description

Returns the number of characters (length) in a given cell.

Syntax

LEN(value)

The LEN function takes the following argument:

  • value—A value or reference to a cell containing a value.

Example

=LEN([Task Name]5)

Result: 6

Notes

  • Formatting (including currency) values aren't included.
  • Dates have a length of 8. If the date column is used for Dependencies, it has a length of 16.

LOOKUP (Advanced Function)

Description

Looks up a value that you specify and returns a corresponding value in the same row but from a different column.

Syntax

LOOKUP(value, collection, return_column_index, collection_match)

The LOOKUP function takes the following arguments:

  • value—A value or reference to a cell containing a value.
  • collection—the cell range, or lookup table, where you want to look up the value. Must contain more than one column. The collection and LOOKUP function can only be used on the same sheet as the lookup table.
  • return_column_index—the column number of the column that contains the value that you want to return. The leftmost column in the range is in index position 1.
  • collection_match (optional)—accepts a value of true or false. Specifies how the lookup will be performed:
    • true (default value)—assumes that the list is sorted ascending and returns the nearest match that is less than or equal to ( <= ) the lookup value.
    • false—returns the first exact match.

Example

=LOOKUP("Task E", [Task Name]1:Done5, 2, false)

Result: 425.75 (the cost of Task E)

Notes

  • To look up text strings, you must enclose the lookup value in quotation marks (for example, “Task E”).
  • You must enter the LOOKUP function on the same sheet as the collection (lookup table) that it references.
  • If the values in the lookup range are not sorted in alphanumeric order, you must use the optional range_lookup parameter to perform a successful lookup.

LOWER (Text Function)

Description

Converts any uppercase characters to lowercase.

Syntax

LOWER(value)

The LOWER function takes the following argument:

  • value—A value (typically a text string) or reference to a cell containing a value.

Example

=LOWER([Task Name]4)

Result: task d


MATCH (Advanced Function)

Description

Returns the index number found in the lookup table for a matching value in an array. The first item in the lookup table is position 1.

Syntax

MATCH(value, range, match_type)

The MATCH function takes the following arguments:

  • value —A value or reference to a cell containing a value.
  • range the collection of cells where you want to look up the data. (The lookup range must be located on the sheet in which you’re creating the formula.)
  • match_type (optional)—how to perform the search. Accepts values 1, 0, or -1.
    • 1—Find the largest value less than or equal ( <= ) to the lookup value (requires that the range be sorted in ascending order)
    • 0—Find the first exact match (the range may be unordered)
    • -1—Find the smallest value greater than or equal to (>=) the lookup value (requires that the range be sorted in descending order)

Example

=MATCH("Task C", [Task Name]1:Done5)

Result: 9 (the index position of Task C in the range of sample data)

Notes

  • If the values in the lookup range are not sorted in alphanumeric order, you must use the optional match_type parameter to perform a successful lookup.

MAX (Numeric Function)

Description

Returns the highest numeric value or latest date.

Syntax

MAX(range)

The MAX function takes the following argument:

  • range—A collection of cells from which you want to return the highest value.

Example

Example: =MAX(Cost1:Cost5)

Result: 425.75


MID (Text Function)

Description

Returns a substring based on a given starting point and number of characters.

Syntax

MID(cell_reference, start_position, number_of_characters)

The MID function takes the following arguments:

  • cell_reference—Cell you want to extract the substring from.
  • start_position—Starting from the leftmost, the number of the character you wish to start extracting from.
  • number_of_characters—Starting from the leftmost and depending on the start_position, the total number of characters you want to extract.

Example

=MID([Task Name]5, 2, 5)

Result: ask E


MIN (Numeric Function)

Description

Returns the lowest numeric value or earliest date.

Syntax

MIN(collection)

The MIN function takes the following argument:

  • collection—Range of cells in which you want to find the lowest value.

Example

=MIN(Cost1:Cost5)

Result: 100


MONTH (Date Function)

Description

Returns an integer from a date as a month number 1-12, in numeric format.

Syntax

MONTH(date_value)

The MONTH function takes the following argument:

  • date_value —The referenced cell (must be of a Date column type) or other date value.

Example

=MONTH([Due Date]5)

Result: 3


NETDAYS (Date Function)

Description

Returns the number of days between two dates.

Syntax

NETDAYS(start_date, end_date)

The NETDAYS function takes the following arguments:

  • start_date—First date (must be from a Date column type) to be measured.
  • end_date—Last date (must be from a Date column type) to be measured.

Example

=NETDAYS([Due Date]4, [Due Date]5)

Result: 29


NETWORKDAY (Date Function)

Description

Returns the number of working days between a start date and end date, but adds 1 to the result if the first argument is a non-working day.

Syntax

NETWORKDAY(start_date, end_date, holiday_range)

The NETWORKDAY function takes the following arguments:

  • start_date—First date (must be from a Date column type) to be measured.
  • end_date—Last date (must be from a Date column type) to be measured.
  • holiday_range (optional)—the dates to exclude from the count (for example, holidays or other specific dates).

Example

=NETWORKDAY([Due Date]4, [Due Date]5)

Result: 21

Notes

  • By default, the WORKDAY, NETWORKDAY, and NETWORKDAYS formulas count Saturday and Sunday as non-working days. If dependencies are enabled on your sheet you can customize the non-working days, and the formulas will use your settings in calculations.
  • You can designate additional dates as nonworking to exclude them when calculating the number of working days. To do this, enter each holiday/non-working day into a cell and then reference the range of cells in your NETWORKDAY formula.

NETWORKDAYS (Date Function)

Description

Returns the number of working days between a start date and an end date.

Syntax

NETWORKDAYS(start_date, end_date, holiday_range)

The NETWORKDAYS function takes the following arguments:

  • start_date—First date (must be from a Date column type) to be measured.
  • end_date—Last date (must be from a Date column type) to be measured.
  • holiday_range (optional)—The dates to exclude from the count (for example, holidays or other specific dates).

Example

=NETWORKDAYS([Due Date]4, [Due Date]5)

Result: 20

=NETWORKDAYS([Due Date]1, [Due Date]5, [Due Date]2:[Due Date]3)

Result: 85

Notes

  • By default, the WORKDAY, NETWORKDAY, and NETWORKDAYS formulas count Saturday and Sunday as non-working days. If dependencies are enabled on your sheet you can customize the non-working days, and the formulas will use your settings in calculations.

NOT (Logic Function)

Description

Performs a logical evaluation on the supplied boolean expression or cell reference. Returns a value of true if the argument is false. This function can be used directly in a boolean column (checkbox, flag, star) or can be used within another function, such as IF, SUM, SUMIF, COUNT, or COUNTIF.

Syntax

NOT(boolean_expression)

The NOT function takes the following argument:

  • boolean_expression—Expression that evaluates to either true or false. Can be a manually typed formula, or a reference to a cell containing values.

Example

=IF(NOT(Done1), "Task A Not Complete", "Task A Complete")

Result: Task A Complete


OR (Logic Function)

Description

Performs a logical evaluation on the supplied boolean expression or cells. Returns true if any condition is met; otherwise returns false. This function can be used directly in a boolean column (checkbox, flag, star) or can be used within another function, such as IF, SUM, SUMIF, COUNT, or COUNTIF.

Syntax

OR(boolean_expression, boolean_expression, ...)

The OR function takes the following argument:

  • boolean_expression—Expression that evaluates to either true or false. Can be a manually typed formula, or references to cells containing values. Typically, multiple expressions are used separated by commas.

Example

=IF(OR([Due Date]1 > [Due Date]2, [Due Date]1 > [Due Date]3), "Due Date 1 isn't the smallest", "Due Date 1 is the smallest")

Result: Due Date 1 is the smallest


PARENT (Advanced Function)

Description

Returns the parent of the specified cell.

Syntax

PARENT(cell_reference)

The PARENT function takes the following argument:

  • cell_reference (optional)—Refer to a specific cell to identify its parent. (If no cell is specified, returns the parent of the current row.)

Example

=PARENT([Task Name]6)

Result: Tasks


PRORATE (Advanced Function)

Description

Used to determine what portion of a value is applied to a particular period of time.

Syntax

PRORATE(value, value_start, value_end, prorate_start, prorate_end, decimal_place)

The PRORATE function takes the following arguments:

  • numeric_value —numeric value or reference to a cell containing numeric value to prorate
  • value_start —first date value or reference to a cell containing the date value
  • value_end —last date value or reference to a cell containing the date value
  • prorate_start—first date value or reference to a cell containing the date value to prorate
  • prorate_end—last date value or reference to a cell containing the date value to prorate
  • decimal_place (optional)—returns prorated value with specified decimal place with 1 being tenths, 2 being hundredths, etc.

Example

=PRORATE(Cost1, [Due Date]1, [Due Date]4, [Due Date]3, [Due Date]4)

Result: 34.41

=PRORATE(Cost1, [Due Date]1, [Due Date]4, [Due Date]3, [Due Date]4, 1)

Result: 34.4


REPLACE (Text Function)

Description

Replaces a string of characters, starting at a given position and ending a given number of spaces beyond, with a new string.

Syntax

REPLACE(text_value, start_position, number_of_characters, replacement_value)

The REPLACE function takes the following arguments:

  • text_value—A text value or reference to a cell containing the text value you want to replace.
  • start_position—Beginning from the left, the character starting position (including spaces) you want to replace.
  • number_of_characters—The number of characters that you want to replace.
  • replacement_value—The text value you want to use when replacing.

Example

=REPLACE([Task Name]4, 6, 1, "W")

Result: Task W


RIGHT (Text Function)

Description

Returns the number of characters stated, starting from the rightmost character in the string.

Syntax

RIGHT(value, number_of_characters)

The RIGHT function takes the following arguments:

  • value—The cell containing the characters you want to return.
  • number_of_characters (optional)—Starting from the rightmost character, the number of characters that you want to return. If omitted the value is 1.

Example

=RIGHT([Task Name]5,3)

Result: k E


ROUND (Numeric Function)

Description

Rounds a given number to the desired number of digits.

Syntax

ROUND(numeric_value, decimal_places)

The ROUND function takes the following arguments:

  • numeric_value—A value (must be a number) or reference to a cell containing a value.
  • decimal_places (optional)—The number of digits to which you want to round the number. The default decimal places is 0 if this argument is omitted.

Example

=ROUND(Cost5, 1)

Result: 425.8


SUBSTITUTE (Text Function)

Description

Replaces the existing text with new text in a text string.

Syntax

SUBSTITUTE(cell_reference, current_text, replacement_text, replacement_occurence)

The SUBSTITUTE function takes the following arguments:

  • value—A value (typically a text value) or reference to a cell containing a value.
  • current_text—String (single or multiple characters) you want to have replaced, surrounded by quotation marks.
  • replacement_text—String (single or multiple characters) you want to replace from the current text argument, surrounded by quotation marks.
  • replacement_occurence (optional)—If a text string occurs multiple times in the cell, specifies the instance to be substituted.

Example

Given the text “hello” in the referenced cell.

=SUBSTITUTE([Column Name]1, “l”, “v”, 1)

Result: hevlo

Given the text “hello” in the referenced cell.

=SUBSTITUTE([Column]1, “l”, “z”, 2)

Result: helzo

Notes

  • Text values are case sensitive.

SUM (Numeric Function)

Description

Adds a collection of numeric values.

Syntax

SUM(numeric_collection)

The SUM formula takes the following argument:

  • numeric_collection—A group of values or references to cells containing values. Can be a range or individual cell references, separated by commas.

Example

=SUM(Cost1:Cost5)

Result: 1125.75

=SUM(Cost1, Cost3, Cost5)

Result: 725.75


SUMIF (Advanced Function)

Description

Adds numbers within a range which meet a criterion.

Syntax

SUMIF(criterion_range, criterion, sum_range)

The SUMIF function takes the following arguments:

  • criterion_range—The group of cells containing the criterion you want to evaluate.
  • criterion—A value or reference to a cell containing a value.
  • sum_range—The group of cells (typically adjacent to the criterion range) you want to sum, assuming they meet the criterion.

Example

=SUMIF(Done:Done, 1, Cost:Cost)

Result: 300

=SUMIF(Cost:Cost, < 200)

Result: 250


SUMIFS (Advanced Function)

Description

Adds numbers within specific cells or range that meet criteria.

Syntax

SUMIFS(sum_range, criterion_range, criterion, criterion_range, criterion, ...)

The SUMIFS function takes the following arguments:

  • sum_range—Collection of cells (typically adjacent to the criterion range) that you want to sum, assuming they meet all criteria.
  • criterion_range—Collection of cells you want to evaluate with the criterion.
  • criterion—Value, such as a text string or number, that you want to locate in the criterion range.

Example

=SUMIFS(Cost:Cost, Done:Done, 1, [Task Name]:[Task Name], "Task A")

Result: 100


TODAY (Date Function)

Description

Returns the current date. Displays today's date in a Date column. When used in other formulas, it can be used to compare other dates with the current date.

Syntax

TODAY(numeric_value)

The TODAY function takes the following argument:

  • numeric_value (optional)—Number of days before (negative number) or after (positive number) the current day.

Example

Dates in the following examples are in US format (mm/dd/yy).

=TODAY(-5)

Returns five days before the current date. If today’s date is 11/8/16, the result would be 11/3/16.

=TODAY(5)

Returns five days after the current date. If today’s date is 11/8/16, the result would be 11/13/16.

=[Due Date]1 - TODAY()

Result: The number of days between today and 11/01/13.


UPPER (Text Function)

Description

Converts any lowercase characters to uppercase.

Syntax

UPPER(text_value)

The UPPER function takes the following argument:

  • text_value—A text value or reference to a cell containing a value.

Example

=UPPER([Task Name]4)

Result: TASK D


VALUE (Text Function)

Description

Converts a text value (that represents a number) to a number.

Syntax

VALUE(text_value)

The VALUE function takes the following argument:

  • text_value—A text value or reference to a cell containing a text value.

Example

=VALUE(LEFT(Cost5, 3)) * 4

Result: 1700

Notes

  • You can use the VALUE function within another function to ensure that a numeric value will be returned from your formula. Certain formulas will always return a value in text format, even if it looks like a number. This will return an error when you attempt to perform numeric calculations on a text value, such as in the example below:

    =LEFT(Cost5, 3) * 4
    Result: #INVALID DATA TYPE

    The =LEFT(Cost5, 3) portion of the formula returns the value 425, which looks like a number but is actually stored in Smartsheet as text. Use the VALUE formula to convert text to numeric format so that the formula doesn’t produce an error.

WEEKDAY (Date Function)

Description

Returns the number for the day of the week, where Sunday equals 1.

Syntax

WEEKDAY(date_value)

The WEEKDAY function takes the following argument:

  • date_value—The referenced cell (must be of a Date column type) or other date value.

Example

=WEEKDAY([Due Date]5)

Result: 7


WEEKNUMBER (Date Function)

Description

Returns the number of the week in the year.

Syntax

WEEKNUMBER(date_value)

The WEEKNUMBER function takes the following argument:

  • date_value—The referenced cell (must be of a Date column type) or other date value.

Example

=WEEKNUMBER([Due Date]5)

Result: 9


WORKDAY (Date Function)

Description

Returns a date that is the specified number of working days before or after a date.

Syntax

WORKDAY(date_value, number_of_days, holiday_range)

The WORKDAY function takes the following arguments:

  • date_value—The referenced cell (must be of a Date column type) or other date value.
  • number_of_days—A numeric value representing the number of days before (negative number) or after (positive number) the referenced date cell.
  • holiday_range (optional)—A group of cells (must be of Date column type) containing holidays to exclude.

Example

=WORKDAY([Due Date]1, 365, [Due Date]2:[Due Date]3)

Result: 3/30/15 (US date format)

=WORKDAY([Due Date]5, 6)

Result: 3/10/14 (US date format)

Notes

  • By default, the WORKDAY, NETWORKDAY, and NETWORKDAYS formulas count Saturday and Sunday as non-working days. If dependencies are enabled on your sheet you can customize the non-working days, and the formulas will use your settings in calculations.
  • You can designate other dates as nonworking to exclude them when calculating the new date. To do this, enter each holiday/non-working day into a cell and then reference the range of cells in your WORKDAY formula.

YEAR (Date Function)

Description

Returns an integer as four digit year, in numeric format.

Syntax

YEAR(date_value)

The YEAR function takes the following argument:

  • date_value—The referenced cell (must be of a Date column type) or other date value.

Example

=YEAR([Due Date]5)

Result: 2014


YEARDAY (Date Function)

Description

Returns the number of the day in the year on which a specified date occurs.

Syntax

YEARDAY(date_value)

The YEARDAY function takes the following argument:

  • date_value—The referenced cell (must be of a Date column type) or other date value.

Example

=YEARDAY([Due Date]5)

Result: 60


Using Hierarchy in Formulas

Hierarchy formulas allow you to roll-up information found on child rows of the referenced parent row. If placed on the parent row in the column with the data to be utilized, the syntax is =COUNT(CHILDREN()) with empty parentheses. If not placed on the parent row above the data to be utilized, you must include a reference to the parent cell within the desired data column e.g.=SUM(CHILDREN([Total Hours]14)).

The following functions can use CHILDREN() as a parameter: AVG(), COUNT(), COUNTIF(), MAX(), MIN(), SUM(), SUMIF(), AVGW().

TIP: Learn about creating parent and child rows in our Hierarchy: Indenting & Outdenting rows article.


Using @cell in Formulas

When you want to perform calculations in formulas that use SUMIF(), SUMIFS(), COUNTIF(), or COUNTIFS(), you can use the @cell parameter in the criteria of the function. The @cell parameter performs a calculation on each row at the same time that the primary function (SUMIF for example) is evaluating the criteria in the range; this makes your formula more efficient.

For example, using the sample data, if you wanted to sum the cost of all tasks whose due date occurred in 2014, you would create the following formula:

=SUMIF([Due Date]1:[Due Date]5, YEAR(@cell) = 2014, Cost1:Cost5)

SUMIF formula using @cell

The result of this formula is $875.75 (the sum of the cost for all tasks whose due date occurred in 2014).


Troubleshooting Formula Errors

Check out our article on Formula Error Messages for details on errors you receive in your formulas.

 

Back to Top