Formulas

To see all the formulas below in action, download the Formula Sample Sheet. Go to the Smartsheet Template Gallery and search on: formula


1. Formula Basics

  • Referencing a cell - Smartsheet cells are referenced by the column name and row number e.g. Budget1 refers to the cell found under the Budget column on row number 1.
  • Multi-word Column Names – If a column name contains a space, use square brackets to refer to the column e.g. [Actual Cost]10
  • Viewing a formula - Selecting a cell with a formula will cause the formula to display below the cell.
  • Typing a formula -  You can manually enter a formula into a cell by starting the formula with an  = sign e.g. =SUM(Budget1:Budget10)
  • Choosing a formula – You can choose a function from the formula toolbar button and click, click-drag, or ctrl+click to select multiple cells to include in the formula
  • Editing a formula - Press F2 while on a cell or double-click

 

2. Numeric Formulas

SUM() SUM(cell1,cell2,...) or SUM(range_start:range_end) Adds set of values or range of cells
AVG() AVG(cell1,cell2,...) or AVG(range_start:range_end) Calculates the average of a set of values or range of cells
MAX() MAX(cell1,cell2,...) or MAX(range_start:range_end) Determines the highest numeric value in a list or range of cells
MIN() MIN(cell1,cell2,...) or MIN(range_start:range_end) Determines the lowest numeric value in a list or range of cells
INT() INT(single_value) Returns the integer portion of a numeric value.
ROUND() ROUND(number, number_of_digits) Rounds number to desired digits. Negative number of digits operates to the left of the decimal (-1 rounds to 10s, -2 rounds to 100s, etc.)
ABS() ABS(single_value) Gives the absolute value of a number which is the number without its sign.
COUNT() COUNT(cell1,cell2,...) or COUNT(range_start:range_end) Returns count of non-blank cells.

 

3. Logical Formulas

IF() IF(logical_test, value_if_true, value_if_false) Returns value_if_true or value_if_false, depending on logical_test. Can be nested.
AND() AND(boolean_expression1, boolean_expression2,...) Evaluates if a set of logical expressions are True or False. If any expression is False it will evaluate as False. Generally used within an IF structure.
NOT() NOT(boolean_expression) Performs a logical NOT on she supplied boolean expression (or cell reference). Generally used within an IF structure.
OR() OR(boolean_expression1, boolean_expression2,... ) Performs a logical OR on the supplied boolean expression or cells. Returns true if any are true; otherwise returns false. Generally used within an IF structure.
ISBLANK() ISBLANK(cell) Tests whether a value exists in a cell and evaluates as True if no value exists. Generally used within an IF structure.
ISTEXT() ISTEXT(cell) Tests whether a cell contains text (a value which is not a number, date, or checkbox). Generally used within an IF structure.
ISNUMBER() ISNUMBER(cell) Tests whether a cell contains numeric data (a value which is not text, date, or checkbox). Generally used within an IF structure.
ISDATE() ISDATE(cell) Tests whether a cell contains date data (a value which is not text, number, or checkbox). Generally used within an IF structure.
ISBOOLEAN() ISBOOLEAN(cell) Tests whether a cell contains boolean data (a value which is not text, number, or date). Generally used within an IF structure.

 

4. Text Formulas

FIND() FIND(expression, text_value) Returns the starting position (number of characters in) where a specified string is found.
REPLACE() REPLACE(text_value, start_position, number_of_characters, new_value) Replaces a string of characters, starting at the given position and going and ending a given number of spaces beyond, with a new string.
SUBSTITUTE() SUBSTITUTE(text_value, old_text, new_text, [optional - number of instances to be replaced]) Replaces existing text with new text in a text string.
LOWER() LOWER(text_value) Converts any uppercase characters to lowercase
UPPER() UPPER(text_value) Converts any lowercase characters to uppercase
LEFT() LEFT(text_value, number_of_characters) Returns the number of characters stated starting with the leftmost character in a string
MID() MID(text_value, start_position, number_of_characters) Returns the number of characters specified from a given starting point in a string.
RIGHT() RIGHT(text_value, number_of_characters) Returns the number of characters stated starting with the rightmost character in a string

 

5. Date Formulas

TODAY() TODAY() Returns the current date. Can only display a date in Date type column but can be used in formulas to compare dates with today's date.
DATE() DATE(year, month, day) Creates a date. Typically used when a formula needs to compute a date as an argument for another function.
DAY() DAY(date) Returns an integer as day of month 1-31
MONTH() MONTH(date) Returns an integer as month 1-12
YEAR() YEAR(date) Returns an integer as four digit year
WORKDAY() WORKDAY(start_date, days, holiday_cell1:holiday_cellx) Returns a date that is the specified number of working days before or after a date. Optionally excludes holidays outlined in a range.
NETDAYS() NETDAYS(date1, date2) Returns the number of days (including start and end date) between two days. Doesn't take in consideration weekends or holidays. Will produce a negative number if date_1 is chronologically after date_2.
NETWORKDAYS() NETWORKDAYS(start_date, end_date, holiday_cell1:holiday_cellx) Returns the number of whole working days between a start date and end date. Optionally excludes holidays outlined in a range.
NETWORKDAY() NETWORKDAY(start_date, end_date, holiday_cell1:holiday_cellx) Same as NETWORKDAYS except that it will add one to the result if the first argument is a Saturday or a Sunday.

 

6. Hierarchy 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 <function>(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(), MAX(), MIN(), SUM().

 

7. Advanced Formulas

AVGW() AVGW(value_range, weight_range) Weighted Average - Calculates the average given a list of numbers and a corresponding list of how many times they should be counted.
PRORATE() PRORATE(value, value_start, value_end, prorate_start, prorate_end, [optional - decimal places]) Used to determine what portion of a value is applied to a particular period of time.
COUNTIF() COUNTIF(criteria_range, criteria_value) Counts the number of cells within a range that meet a single criteria.
COUNTIFS() COUNTIFS(sum_range, criteria_range1, criteria_value1, criteria_range2, criteria_value2...) Applies criteria to cells across multiple ranges and counts the number of times all criteria are met.
SUMIF() SUMIF(criteria_range, criteria_value, sum_range) Adds numbers within a range which meet a specific criteria
SUMIFS() SUMIFS(sum_range, criteria_range1, criteria_value1, criteria_range2, criteria_value2...) Adds numbers within specified cells or range that meet multiple

 

8. Troubleshooting Formula Messages

#BLOCKED The calculation is blocked because at least one of the cells referenced by the formula has an error. Determine which cell referenced by this formula has a problem. That cell will have a more specific error message.
#CIRCULAR REFERENCE The formula references itself. The circular reference may be direct where the reference is in the formula text. However, it is more likely to be indirect - where this formula references a cell which then references back to this cell. Determine which reference is circular. Indirect references can be many levels deep. Sometimes it is easiest to make a copy of the formula and remove cell references until the error is eliminated. This process of elimination will help you see which reference is ultimately circular.
#CONTACT COLUMN DOES NOT SUPPORT FORMULAS You cannot have a formula in a contact column Move your formula to a different column.
#DATE EXPECTED or #NUMBER EXPECTED or #BOOLEAN EXPECTED The formula is in a specific type of column (Date, Number, Symbol) and the returned value is of a different type. Either move the formula to a different column, or convert the result to the appropriate type. Formula results may be converted to a string by adding an empty string. For example = TODAY() + ""
#DIVIDE BY ZERO Your formula attempted to divide an amount by zero ( e.g. 4 / 0 provides a divide by zero error ). Either change your data, or use an IF to avoid dividing by zero. For example, =IF(Value4 <> 0, 100/Value4, "")
#IMBALANCED The parentheses in your formula do not agree. The number of open parentheses is not the same as the number of closed parentheses. Look closely at your formula - make necessary changes.
#INVALID CHARACTER The formula contains an unexpected ']' character. Remove any extra ']' characters. If your column name includes a square bracket character, place a backslash character in front of it to interpret the character properly. For example, for a Column called “Data [1]”, use the following syntax for it in a formula where 4 is the row number: [DATA\[1\]]4
#INVALID DATA TYPE Your formula includes an operation with a set of data types which are not allowed. For example ="Hello" / "World" Check your formula closely for mathematical operations which do not make sense. You could use an IF to avoid this error. For example, =IF(ISNUMBER([Value1]9), [Value1]9 / 100, "")
#MISSING OR INVALID PARAMETERS The supplied parameters are either incomplete or the wrong data type. For example, =TODAY("Hello") Double check your function parameters. If the parameter is a cell reference, you will need to look at the source data to determine it's the correct data type. If a formula has more than one function call (as in IF statements), it is sometimes easier to make a copy and modify the copy using a process of elimination to discover the root issue.
#OPERATOR EXPECTED Your formula has an incomplete mathematical expression. For example =(100 / 3) 10 Closely inspect the formula and make necessary corrections. The example would be correct as =(100 / 3) * 10
#OVERFLOW The formula encountered or computed a value which exceeds the supported numeric range. For numeric values the range is -9007199254740992 throug 9007199254740992. For WORKDAY calculations, maximum number of days is one million. In most cases, this error is caused by inaccurate or incomplete cell references. Check your formula and make necessary changes.
#REF The formula references a cell which does not exist. This is most commonly caused by deleting a row or column from the sheet. Your formulas will need to be updated.
#SEPERATOR EXPECTED A function call has two or more parameters in a row which have not be separated by a comma. For example, =MAX([Value1]15,(3*100) 1000) Check your function calls closely and make necessary adjustments. The example would be correct as =MAX([Value1]15,(3*100), 1000)
#UNEXPECTED OPERATOR Your formula has an incomplete mathematical expression. For example = 100 + Either finish the expression or remove the extra operator. The example would be correct as = 100 + [Value1]15
#UNEXPECTED SEPERATOR A function call has a comma in an unexpected location. For example = MAX(1,2,) Remove extra commas from the formula. The example would be corrected as =MAX(1,2)
#UNPARSEABLE The formula has a problem which prevents it from being parsed and interpreted. This can happen for many reasons such as misspelling a column name or using single quotes instead of double quotes. Check the formula carefully. Pay close attention to column names and quoted text.
#UNRECOGNIZED FUNCTION You have referenced a function which does not exist. For example =MAXIMUM(Value1, Value2) Check the formula names against those in the Formula Syntax lists above.

 

9. Frequently Asked Questions

Q. Can a formula reference a cell in a different Smartsheet?

A. Not at this time.  Smartsheet formulas can only reference cells on the sheet in which the formula exists.


Q.  How can I get a formula to include rows that are newly added?

A. If you are creating a formula to use all values within a column, exclude the row references and only use the column name in the included range (e.g. =SUM([Total Hours]:[Total Hours]). This will ensure that any added rows will be automatically included in the calculation.


Q.  Can symbol type columns be controlled by formulas?

A.  Yes, each of the symbols can be set using values in a formula.

  • For Star, Flag, and Checkbox columns where the symbol is either on or off, you can use a 1 for on or 0 for off (e.g. =IF(Status5 = "Complete", 1, 0)  in a Checkbox would create a checkmark once the status is Complete).
  •  For Red Yellow Green Light columns, you can use the literal values of “Red”, “Yellow”, or “Green” (e.g. =IF(Status5 = "At Risk", “Red”, “Green”)).
  •  Priority columns use the value of “High” or “Low” (e.g. =IF(Status5 = "Overdue", "High", " ")).
  •  Harvey Balls show quarters of a circle by using the values of "Quarter", "Half", "Three Quarter", "Full", or "Empty" (e.g. =IF([% Complete]5 = 1, "Full", "Empty")).

Q.  How come my formula referencing % Complete isn’t working?

A.  In columns that are formatted using the % button on the toolbar, if you turn off the formatting you will find that though a value might display as say 50% the actual value is expressed as the decimal value of .5. In your formula (and also in reporting criteria and conditional formatting), use the decimal value for the percentage (e.g. =IF([% Complete]5 < .25, “At Risk”, “ “)) to properly compare % formatted values.


Q.  Can I modify formulas used in Gantt-specific columns?

A.  When Gantt dependencies are enabled, Gantt columns such as Start Date, End Date, Duration, and % Complete have inherent formulas to provide Gantt charting functionality. It is not recommended to make changes in these columns as they may be overwritten programmatically. Instead, you can create new columns to perform the desired calculations.