Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template to view 100+ formulas, including a glossary of every function and examples of commonly used and advanced formulas.

❓️Product questions
Sign in and ask it here! The community's got your back.

📈 Wondering if there’s a problem?
Check out the Status Page and subscribe for notifications.

💡 Share your knowledge!
Help others by answering questions.

Formulas and Functions

Stumped by a formula? Ask for help or find examples to see how others use a formula to solve a similar problem.

Find Unique cell in a column.
We are trying to move some Excel sheets into Smartsheet! In Excel, we have an address list in a sheet named “Main” and another sheet named “Street“. We extract the unique street names from “Main” and list them in “Street” and we count the number of duplicates of the streets name. My little sample has 7 addresses – the…
JOIN with 2 columns that are NOT next to each other, and "force" order
I am (trying to) using the JOIN function. It works but NOT the way I want it to. First, the columns I wanted to join were not next to each other. I am attempting to join 2 columns in a specific order and NOT the column in between. What am I doing wrong? They need to be in this order as the essential piece of data is the…
Formula error
I want to create a sheet summary of some data. One formula that will count the number of cells in the Status column that contain "SPEC" if the Closed to Builder Box NOT checked. One formula that will count the number of cells in the Status column that contain "SPEC" if the Closed to Builder Box NOT checked.
Help with Health Symbol
I have a formula to populate the health status but would like it to return "Green", regardless of whether there's information in the End Date. It seems to me like this should do it. But as you can see in the below screen shot, when the status is "Complete", the Health field is blank. Here's the text of the formula in case…
How to use automation to copy only certain rows to another sheet?
I have large rows of dataset in a sheet. I am looking for a solution that can copy specific rows to another sheet based on certain criteria. I used the automation template for copying rows, however, on fulfilling the condition specified, the automation all rows in the primary sheet to the secondary sheet. Is there a way I…
INDEX/MATCH and INDEX/COLLECT Issues
Hi, I am essentially trying to combine the two below formulas into one. I keep seeing that I should be able to use a COLLECT formula instead of MATCH to have two criteria, but whatever I do it isn't working. The formulas: =IF(WFX@row <> INDEX(WFX:WFX, MATCH(Quarter@row, Quarter:Quarter, 0)), "Yes", "Applied") =IF(WFX@row =…
Creating a chart from a multi-select dropdown among multiple sheets?
Hi there, I am running into a roadblock on creating a report and graph for the below scenario. We have a bunch of event sheets (about 10) that all have an 'assigned to' column that is a multi-select contact list and it varies between sometimes being assigned to just one person and sometimes to multiple. I am trying to…
Is there a limit to how many rows a function can search, for an IF statement? Having data not show.
For a requisition database, I maintain a pool sheet of available equipment, as well as sheets for orders and the vendor. I have formulas in a different sheet to add the VINs (let's say 5) that I am planning to add and it checks across the other sheets if that VIN exists. On this "check" sheet, I've noticed some VINs will…
Automating Copying/Updating NON-BLANK Cells From One Sheet to Another Using a Unique ID Cell
Hey Everyone! I'm trying to set up a form of automation that's quite a bit more advanced than anything I've done in Smartsheets before and can't seem to find a solution. I'm admittedly pretty new to the advanced functions of Smartsheets. In this scenario, I am using a form to evaluate skills of field engineers. The…
Template and Cross Sheet Reference
I have 5 templates. Each time we have a new project, we select the appropriate template and save as new. The template gets updated frequently (task column), and also new rows are added. Is there a way to use cross reference formula to automatically update the new project smartsheet everytime the template is updated, and…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

If then formula help
Hi All! I have a column , Average Score, that has been calculated using other fields. I want to use the average score to determine if an initiative is a program or project. If the initiative’s average score is between: 5 and 9 it is considered a project and if the score is between 10 and 15, it is considered a program. I…
Dashboard Last Updated Date
I would like to display the last time the dashboard was updated. My approach was adding a date field in the summary data that automatically pulls the last saved date of the sheet. I don't think I can do a formula in the date field type. Any other suggestions?
Counting yes and yes w/notes
Hello, I am having trouble counting using the COUNTIFS formula to yes's and another category called "Yes w/notes". Here is the current formula that I have: • =COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is there a permit required?]@row, @cell = "Yes") / COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is…

Formulas and Functions

Help Article Resources

Categories

Popular Tags

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags