Formulas and Functions

Automatically update the TODAY function in formulas

genevieve.penny — Thu, 05 Jan 2023 14:36:38 +0000

Automatically update the TODAY function in formulas

For the TODAY function to recognize what the current date is, the sheet must be updated and saved. If there is a chance that a sheet may not have enough daily activity for TODAY to update, there are few different workflows you can leverage to automatically update your sheet.

Option 1: Record a date in a helper column

First, add a Date type of column to your sheet and add yesterday's date (or any date in the past) into a cell. You can then hide this column.

Next, create a time-based workflow to add today's date into that new column using the Record a Date action.

To create this workflow:

Select Automation > Create workflow from scratch.
Title your workflow (for example, "Daily Update").
Set the trigger to When a date is reached.
Select Run Once and change this to Custom > Repeat every Day.
Select an early morning time for the trigger (e.g. 2:00AM).
Configure a condition block for where the new date column is in the past.
Set the Action block to Record a date in that date column.

Remember to add at least one past date into your new date column so that your workflow has a cell to update tomorrow.

Option 2: Record a date and use cell links to update multiple sheets

If you have multiple sheets to update, you can set up the date column and workflow described above in a separate reference sheet. Next, use a cell link to bring the date from this reference sheet into your current sheet that contains the TODAY function.

The automation will add a new date to the reference sheet daily, which will in turn update your formula sheet via the cell-link data change.

Option 3: Use Bridge to schedule a daily sheet save

If you have access to Bridge, see the Bridge article explaining the Schedule Daily Sheet Save template.

genevieve.penny Thu, 01/05/2023 - 06:36

Permissions

Users that are the sheet Owner or have Admin sharing permissions can create and edit formulas on a sheet. Users with Editor sharing permissions can also create and edit formulas in unlocked cells on a sheet.

PLANS

Pro
Business
Enterprise

The TODAY function in Smartsheet returns the current date within formulas.

Formula combinations for cross sheet references

Pam Mandel — Tue, 24 May 2022 16:26:31 +0000

Formula combinations for cross sheet references

There are countless ways to combine functions to analyze your data and make it more useful.

Review the comprehensive Function List.
Download the Formula Template.
Ask about your specific use case in the Smartsheet online Community.

Cross-sheet formula solutions

The following formulas combine different functions and work well for cross sheet data gathering. Learn more about referencing data from other sheets.

Any of the criteria referenced in quotes can be substituted with a direct cell reference, such as [Column Name]@row, where the value is located in a cell instead of typed into the formula. Learn how to create a cell or column reference in a formula.

Lookup one cell using a matching value

Bring back a value from one column based on a matching value in a different column to identify the row. Use this instead of a VLOOKUP for better sheet performance, reducing cross-sheet cell references.

Use INDEX and MATCH:

=INDEX({Column to return}, MATCH("Matching Value", {Column with value to match}, 0))

The "Matching Value" will need to be unique across both sheets and stored in the same type of column. For example, if you put a filter on your sheet with that value then only one row should appear. If you have multiple rows with the "Matching Value", the formula will only return the first match it finds. See the two solutions below for possible alternatives in this scenario.

Lookup one cell using multiple criteria

Bring back a value from one column based on multiple matching values in other columns.

Use INDEX and COLLECT:

=INDEX(COLLECT({Column to return}, {Column 1 with value to match}, "Value 1", {Column 2 with value to match}, "Value 2"), 1)

Gather all matching content into one cell

Bring back all cells that match your criteria into one cell. This is similar to using INDEX but it allows you to view all possible matching values if you have more than one row in the source sheet with matching data.

Use JOIN and COLLECT:

=JOIN(COLLECT({Column with values to return}, {Criteria Column 1}, "Criteria 1", {Criteria Column 2}, "Criteria 2"), ", ")

Average with multiple conditions

Average one column based on multiple criteria in other columns; essentially a plural AVERAGEIF, allowing you to add multiple filter conditions to determine what rows to average.

Use AVG and COLLECT:

=AVG(COLLECT({Column to Avg}, {Criteria Column 1}, "Criteria 1", {Criteria Column 2}, "Criteria 2"))

Lookup latest date or highest number with conditions

Bring back the latest date or highest number from one column based on multiple criteria in other columns.

Use MAX and COLLECT:

=MAX(COLLECT({Column to find Max Date or Number}, {Criteria Column 1}, "Criteria 1", {Criteria Column 2}, "Criteria 2"))

You can use MIN instead of MAX if you are looking for the earliest date or lowest number.

Still need help?

Use the Formula Handbook template to find more support resources, and view 100+ formulas, including a glossary of every function that you can practice working with in real time, and examples of commonly used and advanced formulas.

Find examples of how other Smartsheet customers use this function or ask about your specific use case in the Smartsheet online Community.

Ask the Community

Pam Mandel Tue, 05/24/2022 - 09:26

Permissions

The Owner, Admins, and Editors can add or edit references. Viewer access or higher is required for the sheet being referenced.

PLANS

Pro
Business
Enterprise

This article includes some frequently used functions and additional resources to help you make the most out of formulas.

View, modify, or delete cross sheet references in formulas

Pam Mandel — Tue, 24 May 2022 16:16:27 +0000

View, modify, or delete cross sheet references in formulas

Edit the formula

You can edit a reference directly in your formula without opening the Sheet Reference Manager.

Select the cell that contains your formula. A summary of the formula appears in the cell.
Click the name of the reference that you’d like to edit.
You will see a link to Edit Reference. This will open the reference another sheet dialog where you can change the reference name or choose a new sheet or cell range.

When you edit a reference, all its instances in the sheet will update in real-time. If want to keep the structure from a formula in your sheet but want to change the range, it’s better to delete the text from the current reference and create a new reference.

Edit in the reference manager

In the sheet reference manager, you can see and modify all cross sheet references and cell link references in your sheet. To open it, right-click any cell in your sheet and select Manage References.

The cross sheet references manager opens by default, but you can switch to the cell link references manager using the left panel. Learn more about cell links.

Use the following table for details on each of the features in the cross sheet references manager:

Feature	Action	Tips
Search	In the search list… box, type keywords to find references you need to use.	Use a conventional naming structure for your references, such as sheet name_columns referenced.
Filter	Open the All References drop-down list to set the filter to Active, Processing, Broken, In Use, or Unused.	Filters help you find, categorize, or review references. Active: Up-to-date references Processing: References loading updates from the source data Broken: Deleted source rows, columns, or cells In Use: References you’re using in cross-sheet formulas Unused: References that are not in any formulas in your sheet
Create	To create a new reference, select + Create.	You can use this reference when you create new formulas.
Edit Reference	Steps in editing references: To display the Menu icon , hover over any reference in the list. Then, select Edit Reference to change the name, referenced cell range, or source sheet.	If the reference you are editing is in use, all its instances in your formulas will be updated.
Duplicate Reference	To make a copy of a reference, select Menu > Duplicate Reference.	The name of the duplicate reference will be Copy of_original reference name.
Delete Reference	To delete a reference, select Menu > Delete Reference.	If the deleted reference is in use, the formulas may return #INVALID REF errors.

Still need help?

Ask the Community

Pam Mandel Tue, 05/24/2022 - 09:16

Permissions

You must have Owner, Admin, or Editor with sharing permissions on a sheet to edit the reference. You must also have Viewer-level sharing permissions or greater on the sheet containing the data you want to reference.

PLANS

Pro
Business
Enterprise

You can make changes to your reference directly in the formula, or, you can use the reference manager.

Create cross sheet references to work with data in another sheet

Pam Mandel — Tue, 24 May 2022 15:39:39 +0000

Create cross sheet references to work with data in another sheet

Each cross sheet reference links to a single sheet. You can create multiple references to pull data from multiple sheets.

Create a cross sheet reference

Build the formula in the cell where you want the data to appear.
For example, type =COUNT(
The formula help card will appear above or below your cell.
In the help card, click Reference Another Sheet.
Search for the sheet where the data exists.
In the search results, select the source sheet.
Select the cell range containing the data you want to reference, and click Insert Reference.
Select a column header to reference the whole column even if rows get added or deleted from the source. To reference multiple columns, hold the Shift key, then select the first and last column - you’ll get everything in between. You can only select contiguous columns. To select individual columns, create multiple references.
Optional: In the Sheet reference name text box, type a name for your reference. If you don’t enter a name, the reference will have a default name based on the sheet name and the number of times that sheet is referenced. The name doesn’t impact the sheet; it’s so you can identify the reference in the formula.

That’s it! You should now see your reference added to your formula. Here’s an example of how your formula should appear:

=COUNT({my_sheet1 Range1})

Tips for working with references

Don’t include any data you don’t need to see in the destination sheet. All information in the reference range is available and may appear in the destination sheet. Try using an INDEX(MATCH formula instead of VLOOKUP, which might reference unnecessary data.
Ensure new rows are in the reference range. Rows added below a referenced range won’t be included. To include all rows, select the columns as the reference.
Re-use references: you don’t have to open the Reference Another Sheet dialog to use an existing reference. In the new formula, add braces { }, then type or paste the reference name in them.
References are unique to the sheet where they were created. You have to make new references for each sheet.
To prevent infinite approval loops, cells that contain cross-sheet formulas or cell links will not trigger automation that changes the sheet (e.g., Move row, Copy row, Lock row, etc.). To work around this, consider using time-based or recurring automated workflows.

Still need help?

Ask the Community

Pam Mandel Tue, 05/24/2022 - 08:39

Permissions

The Owner, Admins, and Editors can add or edit references. Viewer access or higher is required for the sheet being referenced.

PLANS

Pro
Business
Enterprise

Create and edit formulas in Smartsheet

Shaine Greenwood — Mon, 23 Jan 2017 21:12:10 +0000

Create and edit formulas in Smartsheet

Create a formula

You can create a formula in the following field types:

Fields not being used for dependencies or resource management. Learn how to enable dependencies and use predecessors.
Text/Number (as long as they're not being used in dependencies or resource management)
Contact List fields (as long as they're not being used in resource management)
Date (as long as they're not being used in dependencies or resource management)
Dropdown List
Checkbox
Symbol
% Allocation

To place a formula in a field:

Select the desired field.
Type an equal sign in the field, and then type the calculation.
After you’ve typed in your formula, press [Enter].

The result is displayed in the field containing the formula.

Formulas can be placed in Contact List fields. Automatically assign people to tasks with a VLOOKUP formula, for example. Note that formulas can’t be placed in Contact List fields being used for resource management.

Functions

Formulas can include functions, such as SUM, AVG, or IF. Functions perform calculations with or manipulate data in sheets. For the complete list of functions available in Smartsheet, see the Smartsheet Functions List.

Formula operators

You can use the following operators in Smartsheet formulas.

Symbol	Description
+	Add
-	Subtract
*	Multiply
/	Divide
^	Exponent
<	Less than
>	Greater than
>=	Greater than or equal to
<=	Less than or equal to
=	Equal to
<>	Not equal to

Edit an existing formula

To edit an existing formula:

Double-click a field containing the formula to open it for editing.
Make your desired formula changes, and then press Enter.

If you change your mind about editing the formula, press Esc to exit edit mode and revert to the pre-edited formula.

Tips for easier formula creation

Use column formulas

When you need a formula applied consistently and uniformly to an entire column, column formulas are the perfect solution. Build your formula in a cell, and quickly convert it to apply to every cell in the column. Regardless of how new rows get inserted into the sheet, the column formula will automatically apply. Learn how to set formulas for all rows with column formulas.

Use the sum tool

If you're not sure which function to use, you can click the down arrow on the right of the Sum button in the toolbar and select a function.

The Sum button will attempt to provide you with a formula based on the field you have selected in the sheet and any hierarchical relationships with the selected field. For example, selecting the field of a parent row, then clicking the Sum icon will produce a =SUM(CHILDREN()) formula in the field.

After you create a formula, you can modify it at any time by double-clicking in the selected field or by pressing F2 (fn + F2 on a Mac).

Work with percentages

Smartsheet treats percentages as values between 0 and 1. When you create formulas in columns formatted for percent (using the Percentage Format button in the toolbar), use decimal values. For example...

=0.5 + 0.4

...will return 90% in a column formatted for percentage...

=5 + 4

...will return 900% in a column formatted for percentage.

Copy a formula with drag-fill or auto-fill

If you have a formula that you’d like to use in multiple fields without having to manually type the formula in each field, use the following methods to quickly copy the formula to other areas of your sheet:

Drag-fill—You can drag from the lower-right corner of a selected field to copy a formula across contiguous fields in the sheet. As you copy, the formula will automatically change its respective field references.
Auto-fill—You can have Smartsheet automatically copy a formula to new, vertically adjacent fields that enter the sheet. When auto-filled, the new formula will automatically change its respective field references.

Learn how to drag-fill and auto-filling formulas and formatting.

Areas where formula use is restricted

These fields can't contain formulas:

Contact list fields that are being used in resource management
System fields (for example, Modified By)
Default columns (attachments, comments, row action indicator)
The following fields being used for dependencies and resource management:
- Start Date
- End Date
- Duration
- Predecessors
- % Complete

You can't create formulas in these features:

Forms
Update requests (Formula results can still be viewed in update requests, but they can't contain standalone formulas.)
Reports (Formula results can still be viewed in reports, but they can't contain standalone formulas.)
Dashboards (Formula results can be viewed in dashboards, but dashboards can't contain standalone formulas.)

Formulas can't be edited from the following features in Smartsheet:

Forms
Update requests (Formula results can still be viewed in update requests, but the formulas can't be edited.)
Reports (Formula results can still be viewed in reports, but the formulas can't be edited.)
Dashboards (Formula results can be viewed in dashboards, but the formulas can't be edited.)

Still need help?

Ask the Community

Shaine Greenwood Mon, 01/23/2017 - 13:12

Permissions

The sheet Owner and Admins can create and edit formulas in locked and unlocked fields. The Owner, Admins, and Editors can create and edit formulas in unlocked fields.

PLANS

Smartsheet
Pro
Business
Enterprise

Use formulas to calculate numeric values or automate aspects of your sheet. You can create formulas, for example, to sum a range of expenses, or to check a box when a specific value is entered in a field.

Create a cell or column reference in a formula

gwyneth.casazza@smartsheet.com — Mon, 23 Jul 2018 20:14:47 +0000

Create a cell or column reference in a formula

You can create a reference to an individual cell, a range of cells, or an entire column.

It's also possible to reference data from other sheets. Learn more about referencing data from other sheets.

Summary of formula reference types

Here’s a cheat sheet you can use as you build formulas of your own.

To reference this	Format it this way	Example
Individual cell	Column name, row number	=Budget1
Column name contains a space or ends in a number	Enclose the column name in brackets.	=[Column A]1 =DAY([Q1]1)
Absolute reference (always refers to that specific cell, row, or column)	Type a $ symbol in front of the column name, row number or both.	=$[Column A]$1 =[Column B]$1 =$[Column C]1
Multiple, discontinuous cells	Use a comma between cell references.	=SUM(Budget1, Expenses4, [Projected Earnings]20)
A range of cells in the same column	Reference the first cell in the range, then the last cell, separated by a :(colon).	=SUM(Budget1:Budget12)
An entire column (including any newly added cells)	The column name separated by a : (colon).	=SUM(Budget:Budget)
A range of cells across multiple columns	Reference the upper-rightmost cell, then the lower-leftmost cell, separated by a : (colon).	=SUM(January1:March5)
An individual cell, range of cells, or complete columns from another sheet	Type the name of a previously created reference to another sheet, wrapped in curly braces.	=COUNT({my_sheet1 Range1})

More detailed description of reference types

Reference individual cells

While building your formula, you can select a cell to reference it and work with that cell’s data in your formula. (You can also manually type the column name and row number to reference the cell.)

For example, the formula in the Inventory Value column of the following inventory management sheet will multiply the value from row 1 of the Price column to the value in row 1 of the Stock column:

The formula returns $2,994.00, the total worth of that item:

Reference column names that have spaces or end in numbers

If a column name contains spaces or contains any special characters or numbers, you must enclose it in brackets to avoid ambiguity:

=[Annual Budget]1 + [Annual Budget]2

=[Q1]1 + [Q2]1

=[Risk/Issue]5 + [Contingency/Mitigation]5

Create an absolute reference

You may have situations, such as when referencing a table with the VLOOKUP function, where you need to prevent Smartsheet from automatically updating cell references when a formula is moved or copied. To do this, create an absolute cell reference. Learn more about function reference.

To create an absolute reference, type $ (a dollar sign) in front of the column name or row number in the cell reference of your formula. For example, if you move or copy the following formula, the column names and row numbers will not change for the cell references:

=$[Column A]$1 * $[Column B]$1

The following formula has absolute references to only the row numbers. If you move or copy the formula, the column references will change respectively based on the formula’s new location:

=[Column A]$1 * [Column B]$1

The following formula will maintain absolute references to the columns. If you move or copy the formula, the row numbers will change respectively based on the formula’s new location:

=$[Column A]1 * $[Column B]1

Reference a range of cells in the same column

To reference a range of cells, type a : (colon) between the two cell references.

For example, the formula at the bottom of the Inventory Value column of the following inventory management sheet will sum the values from row 1 to row 6 in the same column:

The formula returns $40,763.75, the total of all inventory values:

Reference a whole column

You can reference an entire column in a formula, including all populated cells in the column. For example, the following formula sums all values in the Annual Budget column and will remain updated as new rows are added to or removed from the column:

=SUM([Annual Budget]:[Annual Budget])

If a formula is placed in the column it's referencing, it won't reference the cell that the formula is in. For example, if you were to place the example formula above in a column named Annual Budget, the SUM formula would sum all cells except for the cell containing the formula.

Reference a range across multiple columns

To reference a range of values in a row across multiple columns, reference the first and last column in the row.

For example, the formula in the Total Stock column of the following inventory management sheet will sum the values from the Stock A, Stock B, and Stock C columns on row 1:

The formula returns 998, the total stock from the three locations:

Still need help?

Ask the Community

gwyneth.casazz… Mon, 07/23/2018 - 13:14

Permissions

PLANS

Smartsheet
Pro
Business
Enterprise

When you create formulas, it's possible to include values from other cells or columns on the sheet.

FAQs: Using formulas

gwyneth.casazza@smartsheet.com — Thu, 07 Jul 2016 22:03:06 +0000

FAQs: Using formulas

Still need help?

Ask the Community

gwyneth.casazz… Thu, 07/07/2016 - 15:03

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 with cross-sheet formulas.

Does Smartsheet support a VLOOKUP function?

We do! However Smartsheet has an INDEX and MATCH combination that is more versatile. This combination reduces cross-sheet cell references by looking at columns individually instead of using one large range.

Take a look at our article on formula combinations for cross sheet references for an example.

You can still learn about Smartsheet's VLOOKUP function in this VLOOKUP article as well, or find examples of how other Smartsheet customers gather data using formulas in the Smartsheet online Community.

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.

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 ensures 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.

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

Yes. The character added in the formula depends on the symbols you use. Refer to this list for what to add in the formula:

Star, flag, and checkbox symbols: For these symbols, use 1 for on or 0 for off
- Example: = IF([Status]@row = "Complete",1,0)
Red, yellow, and green light symbols: Use the literal values Red, Yellow, or Green
- Example: =IF([Status]@row = "At Risk", “Red”, "Green")
Priority symbols: Use High or Low
- Example: =IF([Status]@row = "Overdue", "High", "Low")
Harvey Balls: Use Quarter, Half, Three Quarter, Full, or Empty
- Example: =IF([% Complete]@row = 1, "Full", "Empty")

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

These are three likely reasons why it’s appearing as text in a cell:

It’s missing the equal sign (=): Formulas must always start with =
Dependencies are enabled in the sheet: When dependencies are enabled on a sheet, formulas aren't allowed in these dependency-enabled columns
- Start
- Duration
- Finish
- Predecessors
- % Complete or % Allocation: These columns automatically generate values based on the dependency settings and overwrite formulas. If you type a formula in one of these columns, it’ll 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 and iOS apps. Any formula you enter from reports or from a mobile app will display as plain text rather than calculate or find values.

Why isn’t my formula referencing % Complete working?

Cells in sheets or reports use the decimal value, not the percentage format. This is true even for columns using percent-formatted values.

To compare percent values or reference percent columns, refer to the table below.

Instead of this	Do this
=IF([% Complete]@row < 25%, "At Risk", " ")	=IF([% Complete]@row < .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. The time isn't displayed in the column, but it can affect results of formulas.

For example, you have a formula that subtracts TODAY() from the start date—which is today. You would generally expect a result of 0. 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 only looks at the date, not the time when performing the calculation.

I’m seeing inaccurate results when I compute the difference between the Created Date and Modified Date columns. How can I fix it?

Use the DATEONLY function. Dates in System columns are a display value and not the actual value. System columns record UTC dates, but they display your local date/time based on the UTC date. You may see results you don’t expect if you don’t use DATEONLY function.

When you use DATEONLY, it extracts the date from the System column. Here’s a sample formula: =NETWORKDAYS(DATEONLY(Created@row), TODAY()).

Why isn't my formula with TODAY() updating to today's date?

Opening reports and dashboards that reference a sheet with a formula using the TODAY function will not update the TODAY function to the current date. There must be an update to the underlying sheet itself.

You can see a list of actions that will update the formula in the TODAY Function article.

To automatically update your underlying sheet without opening it, take a look at the Automatically update the TODAY function in formulas article or use Bridge to Schedule Daily Sheet Save.

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.

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 has a character that would turn the numeric value into a text value. For example, if the value 12 had an (‘) apostrophe before it, this undesired result occurs.

A trick to catch this is to check the numeric values formatting alignment. Often, values being considered as a text value appear left aligned in the cell, instead of the normal right alignment for numeric values.

Numeric values appear left aligned when they’re:

Produced by formulas, and
In the Primary column.

However, these values will still be recognized as numeric values. Unless something in the formula or cell dictates otherwise.

Another option to correct this error is to use the VALUE() function. The VALUE() function converts text strings containing numeric values to numbers.

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

If you’re evaluating the same range of data for multiple criteria, use the OR Function and @cell parameter 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

Formula	Description	Result
=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.00

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, use the OR Function and @cell parameter 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

Formula	Description	Result
=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

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 don’t create an email address, they’ll return as only a text value.

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	Smartsheet - PM
=COUNTIF([Assigned To]:[Assigned To], HAS(@cell, "S Brown"))	Counts the number of rows where the value in the Assigned to column has the value S Brown	2
=VLOOKUP("don.t@domain.com", [Assigned To]:[Date], 2, false)	Returns the date. If the Assigned to column has the email don.t@domain.com look up and produce the value at the same row in the Date column	10/18/20

How do I create a new contact using a formula?

Currently, we don’t have a direct method to create new contacts with formulas. You can create new contacts using multiple columns if the email addresses of the org follow a particular pattern. For example, first.last@domain.com.

To do this, you can have a column for first.last and another for @domain.com. Using the JOIN function can bring these two fields together into the same contact cell, and create the desired contact.

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.) aren’t 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.

PLANS

Smartsheet
Pro
Business
Enterprise

Formulas in Smartsheet save time by calculating data automatically, combining cell contents in a sheet, and helping you keep track of dates, among other things.

Create efficient formulas with @cell and @row

Shaine Greenwood — Fri, 07 Apr 2017 17:34:14 +0000

Create efficient formulas with @cell and @row

As your sheets get larger, having efficiently created formulas becomes more helpful to their performance. Here’s a look at two functions that will help make your formulas more efficient, @cell and @row.

Use @cell to perform smarter cell calculations

When you want to perform calculations in formulas that look at ranges of cells, for example: SUMIF, SUMIFS, COUNTIF, and COUNTIFS, you can use the @cell argument in the criteria of the function. The @cell argument performs a calculation on each row at the same time that the primary function (SUMIF for example) is evaluating the criteria in the range, making your formula more efficient.

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

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

Add the IFERROR function to your formula to ensure that you don't get an error if @cell encounters a blank cell in the formula. For example: =COUNTIF(Date:Date, IFERROR(YEAR(@cell), 0) = 2016)

Use @row to improve formula performance

With @row, you can improve performance with the automatic changing of cell references in a column (for example, the status of your tasks) for a large number of rows.
Let’s say we want to use a formula to change symbols in the status column of this grid automatically:

Here’s an example of a formula you might build to automate the RYG ball color change in your Status column:

=IF(Complete3 <> 1, IF(TODAY() - [Due Date]3 > 0, "Red", IF(TODAY() - [Due Date]3 > -3, "Yellow", "Green")))

The above formula contains multiple references to cells on row 3. Adding a row above row 3, results in Smartsheet automatically updating all of the cell references in the formula to row 4. Smartsheet would also modify formulas in every row below in the same fashion. The more formulas that need to be modified, the more your performance in Smartsheet becomes impacted.

To possibly improve performance in your sheets, substitute the row numbers with @row in your cell references. This is how the formula would look using @row:

=IF(Complete@row <> 1, IF(TODAY() - [Due Date]@row > 0, "Red", IF(TODAY() - [Due Date]@row > -3, "Yellow", "Green")))

Smartsheet won’t need to modify the cell references if the row is moved—resulting in quicker sheet load and save times. You can also copy and paste this formula without having to manually change the row numbers on cell references, saving you time when you need to copy your formulas.

Still need help?

Ask the Community

Shaine Greenwood Fri, 04/07/2017 - 10:34

Permissions

PLANS

Smartsheet
Pro
Business
Enterprise

Optimize your formulas to improve the overall performance of your sheet and prevent having to manually reference cells in formulas.

Combine (concatenate) text or values from two or more cells with a simple formula

gwyneth.casazza@smartsheet.com — Mon, 23 Jul 2018 20:44:43 +0000

Combine (concatenate) text or values from two or more cells with a simple formula

Create a simple formula to concatenate text

If you want to combine text from two or more cells into a different cell (concatenate the values), you can do this with a simple formula.

Click in the cell in which you want the result.
Press = (the equal sign).
Click the first cell that you want to refer to in the join and then press + (the plus sign).
Click the second cell that you want to refer to (to join the values together) and press Enter.

To get a sense of the possible ways you can combine cell data, see the examples below.

As an alternative, you can use the JOIN function to combine cell values into a text string. Learn more about the JOIN Function.

Concatenation formula examples

In the following examples, assume that [Task Name]1 contains the text "First Task" and [Task Name]2 contains "Second Task."

= [Task Name]1 + " " + [Task Name]2

Result: First Task Second Task

When you join cell values, you can use delimiters to separate the values in the range:

= [Task Name]1 + "-" + [Task Name]2

Result: First Task-Second Task

= [Task Name]1 + "," + [Task Name]2

Result: First Task,Second Task

If no delimiter value is included, the values from the range will be joined without spaces:

= [Task Name]1 + [Task Name]2

Result: First TaskSecond Task

Still need help?

Ask the Community

gwyneth.casazz… Mon, 07/23/2018 - 13:44

Permissions

The Owner, Admins, and Editors can modify a sheet.

PLANS

Smartsheet
Pro
Business
Enterprise

You can combine multiple cells to join values together in a text chain or series.

Formulas: reference data from other sheets

Shaine Greenwood — Mon, 25 Sep 2017 22:01:38 +0000

Formulas: reference data from other sheets

For example, you might use cross sheet references to

Create a metric sheet for use in chart widgets.
Pull data from one sheet to another without replicating the entire sheet.
Display data without sharing the underlying sheet.

Do you want to work with data in a single sheet? You might want to use sheet summary fields instead.

Before you create cross sheet references

Ready to work with cross-sheet formulas? Keep these things in mind:

You must have the required permissions. See the chart below.
A sheet can include no more than 100 distinct cross-sheet references.
A reference range can include a maximum of 100,000 inbound cells.
The following functions don’t support references from another sheet: CHILDREN, PARENT, ANCESTORS. Using a reference from another sheet with these functions will result in an #UNSUPPORTED CROSS-SHEET FORMULA error in the cell containing the formula.

Required permissions

This chart shows what each user can do with cross sheet formulas in source and destination sheets:

Capability	Owner	Admin	Editor	Viewer
View and reference data in the source sheet	Yes	Yes	Yes	Yes
Insert a formula in the destination sheet	Yes	Yes	Yes	No
Edit a reference in the formula	Yes	Yes	Yes	No
Delete sheet references used in cross-sheet formulas	Yes	Yes	Yes	No

If you have a permission to edit a sheet, be careful in deleting sheet references. Any sheet reference you delete will also be removed from users who have access to the file you changed. When this happens, the data in cells with cross-sheet formulas will be affected.

Before you reference data

Ready to work with cross-sheet formulas? Keep these things in mind:

A sheet can include no more than 100 distinct cross-sheet references.
A reference range can include a maximum of 100,000 inbound cells.
The following functions don’t support references from another sheet: CHILDREN, PARENT, ANCESTORS. Using a reference from another sheet with these functions will result in an #UNSUPPORTED CROSS-SHEET FORMULA error in the cell containing the formula.

If you have permission to edit a sheet, be careful in deleting sheet references. Any sheet reference you delete will also be removed from users who have access to the file you changed. The data in cells with cross-sheet formulas will be affected.

Still need help?

Ask the Community

Shaine Greenwood Mon, 09/25/2017 - 15:01

Permissions

You must be a licensed sheet Owner, Admin or Editor to add or edit references.

Viewer access or higher is required for the sheet being referenced.

PLANS

Smartsheet
Pro
Business
Enterprise

You can perform formula calculations across sheets, using those results to give you a higher level picture of what’s happening with your information.