## Applies to

Smartsheet
Enterprise

Formulas in Smartsheet can save you time by calculating data automatically, by combining cell contents in a sheet, or by helping you keep track of dates (to name just a few examples). Using formulas does take a bit of practice and know-how. To help you get up to speed, this article is a compilation of answers to common questions (FAQs) that customers often have as they’re ramping up.

For specific questions about formulas, we also recommend the Smartsheet Community. The community is full of helpful, knowledgeable people with practical Smartsheet experience asking and answering questions — who knows, someone else may have already solved the problem you’re working on.

Search Community discussions about formulas now >

#### Can a formula reference a cell in a different sheet?

Yes, a formula can reference a single cell, range of cells, or full columns in another sheet, using cross-sheet formulas. Learn more about this in our article Formulas: Reference Data from Other Sheets.

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

If a new row is inserted above, below, or between two rows that include the same formula, the new row will inherit that formula automatically (learn more about this in our article on Auto-filling Formulas and Formatting).

If you're 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.

If you've set up a column formula, the formula will automatically apply to every new row that gets added to the column. For more information, see Set Formulas for All Rows with Column Formulas.

#### Can columns with a symbol column type be controlled by formulas?

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

Clicking the drop-down arrow in the cell containing the symbols will show you the spelling and capitalization to use to reference the values in a formula. Here are some examples:

#### Star, Flag, and Checkbox

For these column types, the symbol is either on or off. You can use a 1 for on or 0 for off. For example, you can place this formula in a checkbox column to create a checked checkmark when the status is Complete, use a formula similar to the following:

=IF([Status]5 = "Complete", 1, 0)

#### Red, Yellow, Green Light

Use the literal values “Red”, “Yellow”, or “Green”, for example:

=IF([Status]5 = "At Risk", “Red”, “Green”))

#### Priority

These columns use the value of “High” or “Low”, for example:

=IF([Status]5 = "Overdue", "High", "Low"))

#### Harvey Balls

These symbols show quarters of a circle by using the values of "Quarter", "Half", "Three Quarter", "Full", or "Empty", for example:

=IF([% Complete]5 = 1, "Full", "Empty") )

#### I typed my formula correctly but it's displaying as text in the cell. What went wrong?

The three most likely reasons for text appearing in a cell rather the formula you expect are:

#### Dependencies are enabled in the sheet

When dependencies are enabled on a sheet, formulas aren't allowed in the dependency-enabled columns: Start, Duration, Finish, Predecessors, % Complete or % Allocation.

The values in these columns are generated automatically based on the dependency settings and would overwrite formulas. If a formula is manually typed into one of these columns, it will be displayed as plain text rather than performing a calculation.

#### You created the formula from a report or a mobile device

We don't currently support creating formulas from reports or from the Android or iOS apps. Any formula entered from reports or from a mobile app will display as plain text rather than performing a calculation.

#### Why isn’t my formula referencing % Complete working?

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 0.5. In your formula (and also in reporting criteria and conditional formatting), use the decimal value for the percentage to properly compare % formatted values. For example: =IF([% Complete]5 < .25, “At Risk”, “ “)

#### Can I create formulas in project sheet columns?

When dependencies are enabled, columns such as Start Date, End Date, Duration, and % Complete have inherent formulas to provide project functionality. Formulas aren't allowed in these columns as they may be overwritten. To work around this behavior, you can either disable dependencies on the sheet or create new columns to perform the desired calculations.

#### My formula referencing the Start/End Date column isn't working. How can I fix it?

When dependencies are enabled on a sheet, the Start Date and End Date columns have an implicit time component. Though the time isn't displayed in the column, it can impact results of formulas. For example, let's say you have a formula that subtracts TODAY() from the Start Date, and your Start Date is today. You would generally expect a result of 0, but what you'd actually get is a decimal value as Smartsheet tries to perform the subtraction based on the implicit time stamp. To work around this issue, use the DATEONLY() function. To go along with the example, the formula

=[Start Date]2 - TODAY()

would be revised to:

=DATEONLY([Start Date]2) - TODAY()

In this case, Smartsheet will only look at the date (and not the time) when performing the calculation.

#### What happens to my formulas if I use the "Move to Another Sheet" or "Copy to Another Sheet" commands to move or copy a row containing a formula to another sheet?

Formulas won't be preserved when you use these commands to move or copy rows. You can however, use keyboard shortcuts to copy and paste formulas. For more information, see the “To copy and paste formulas” section of the “Using Copy & Paste” help article.

#### Why are my numeric values not calculating correctly? For example, when adding 1 + 12 it’s producing the result 112, instead of 13.

Often this calculation error occurs because one of the referenced values is being considered as a text value. To correct this error, check that neither of the values being referenced contains a character that would turn the numeric value into a text value. For example, if the value 12 had an (‘) apostrophe in front of it, this undesired result would occur.

A quick way to identify numeric values being considered as text values is to check the numeric values formatting alignment. Often values that are being considered as a text value will appear left-aligned in the cell, instead of the normal right alignment for numeric values.

Note: Numeric values that are produced by formulas will also present as left-aligned. However, these values will still be considered and recognized as numeric values. Unless something in the formula dictates otherwise.

Numeric values will also appear automatically left-aligned in the Primary Column specifically. The Primary Column can be easily identified because it is the only column header that is formatted BOLD. Numeric values in the Primary Column will still be considered and recognized as numeric values unless something in the cell dictates otherwise.

#### I’m using a SUMIFS formula that should be returning a value, but it is returning a 0.

If you are evaluating the same range of data for multiple criteria, you’ll want to use the OR Function and @cell parameter in order to have the correct value returned.

Item

Quantity

Cost

Warehouse

Shirt

26

20.00

A

Pants

24

50.00

A

Socks

10

10.00

A

Shirt

18

25.00

B

Pants

16

75.00

B

Socks

46

15.00

B

 =SUMIFS(Cost:Cost, Quantity:Quantity, >15, Warehouse:Warehouse, "A", Item:Item, OR(@cell = "Shirt", @cell = "Socks")) Sums the Cost of anything with a Quantity over 15 from Warehouse "A" where the Item is either "Shirt" or "Socks" 20

#### I’m using a COUNTIFS formula that should be returning a value, but it is returning a 0.

If you are evaluating the same range of data for multiple criteria, you’ll want to use the OR Function and @cell parameter in order to have the correct value returned.

 Item Quantity Cost Warehouse Shirt 26 20.00 A Pants 24 50.00 A Socks 10 10.00 A Shirt 18 25.00 B Pants 16 75.00 B Socks 46 15.00 B
 =COUNTIFS(Quantity:Quantity, >15, Warehouse:Warehouse, "A", Item:Item, OR(@cell = "Shirt", @cell = "Socks")) Counts anything with a Quantity over 15 from Warehouse "A" where the Item is either "Shirt" or "Socks" 1

#### What functions work in a contact column?

Functions that can return a contact value work in contact columns.

Here are some sample functions you can use:

• Equals Contact cell: =[Assigned To]1
• VLOOKUP: =VLOOKUP([Assigned To]1,{Departments},2,false)
• PARENT: =PARENT()
• INDEX(MATCH()): =INDEX({Departments},MATCH([Assigned To]1,{Names},0))
• IF: =IF(Urgency1="High",[Urgent on call]1,[Assigned To]1)

Below are some specific examples of these formulas in action.

Sample sheet information:

Row #

Project Name

Assigned To

Date

% Complete

Status

1

Project - E

S Brown

03/25/20

30%

Red

2

Outreach

don.t@domain.com

10/18/20

60%

Yellow

3

Smartsheet - PM

Andy Davis

01/06/20

100%

Green

4

Collection - INT

Johnny Apple, S Brown

02/12/20

20%

Red

Given the table above, here are some examples of using Contact Formulas in a sheet:

Formula

Description

Result

=INDEX([Project Name]:[Project Name], MATCH("Andy Davis", [Assigned To]:[Assigned To]))

Returns the value in the “Project Name” column for the row that contains the value “Andy Davis” in the “Assigned To” column.

(Row 3 applies)

Smartsheet - PM

=COUNTIF([Assigned To]:[Assigned To], HAS(@cell, "S Brown"))

Count the number of rows where the value in the “Assigned To” column has the value “S Brown”.

(Rows 1 & 2 apply)

2

=VLOOKUP("don.t@domain.com", [Assigned To]:[Date], 2, false)

Return the Date. If the “Assigned To” column equals the email “don.t@domain.com” look up and produce the value at the same row in the “Date” column.

(Row 2 applies)

10/18/20

Currently we don’t have a direct method to create new contacts with formulas. Customers in the past have achieved this desired goal utilizing multiple columns if the email addresses of the Org follow a particular pattern. For example, first.last@domain.com

To achieve this you could have a column for first.last and another for @domain.com. Utilizing a JOIN function could bring these two fields together into the same contact cell, creating the desired contact.

#### Will formulas that produce a text value return the value as a text value in a contact cell?

Yes, if the cells being referenced by the formula are text values and do not create an email address, they will return as only a text value.

#### Can I use formulas that output data other than contacts or text values in a contact column?

Formulas that output data types other than contacts or text (e.g. dates, numbers, booleans, etc.) are not supported and will return an error code: #CONTACT EXPECTED. This is similar to how DATE columns work today; formulas calculate to #DATE EXPECTED if they output anything other than dates or text. Checkbox columns have a similar error, #BOOLEAN EXPECTED