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.

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Cross sheet formula to return a date based on drop down category
Hello! I'm super familiar with the INDEX(MATCH) function to copy information from a cell in another sheet based on a unique identifier. But I'm struggling to figure out a cross sheet formula to return a date IF a certain drop down item is selected. Here is what I am looking to do: I have a Unique ID column to Match, I want…
Formula Help
How do I need to write this formula to return the date for Week Day; "Mon" for each week of the year based on the current year? Thanks
Can I not use formula columns in Summary formulas?
I am trying to do counts of cards/rows with various ages so I can show how long some things have sat in our backlog. I created a date column that uses the date received, system generated info and just puts a date for "created" to try to get the formula in summary to work but I still get "unparseable". Here is my formula:…
I need to filter the data to figure out which product is failing the most? formula help?
I am required to break down the data into several categories including which product has failed, how many times it has failed. I want to connect the Part number with the Pass/fail box, what is the formula for this?
How do I create a column formula to pull values from a particular row in other sheets?
I've been playing with MATCH and VLOOKUP formulas, but I can't figure it out. My target sheet contains (among other things) 3 columns: The SHEET NAMES of the sheets that I want to pull from START DATE (blank, want to auto-fill) for a particular task within the source sheet FINISH DATE (blank, want to auto-fill) for a…
Is it possible to use both AND and OR in the same formula?
I am using Parent/Children For the Parent, I want to know if all the Children say "Approved", then the Parent is "Approved", If any Child is blank or says something anything other than approved, then the Parent should be blank. The issue is that I also want to say, if one of the Children says "Approved - Conditional" but…
How to reference a single cell in another sheet as part of a formula
Hello all, I have a sheet that users enter data into monthly, but that data contains all the previous months data as well, so I use a different sheet to pull out that data as part of a formula. In excel that formula is ='Input SO'!B5-sum(B3:B5). This allows me to subtract each previous months data. The issue I'm having is…
Using INDEX/COLLECT to return multiple values
Looking for the proper way to return multiple values using INDEX/COLLECT. I'm assuming JOIN needs to be in there but can't get it to work! Both without and with JOIN, the formula still produces the first value only. This formula matches a project number from one sheet to another, and is supposed to return ALL of the…
Sum formula
The above formula is 'unpars' ... What am I doing wrong? I want to total 4 columns
Simplifying text to another column
Hello! I think I need some sort of IF/OR/AND/THEN formula for what I'm working on. I am working in a sheet that has a status column with 8 different text values. My team wants to simplify the look of this in our external dashboard, so are creating a new column that changes the 8 distinct text values into 4 different…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

Tour Tracking Formula Issue
Hello, I am currently using a formula to track how many tours each tour guide has led. I have their names on a sheet next to each tour they have been assigned to. I am using =COUNTIF({Tour Database Range 1}, "Name") and inserting each person's name into the "name" portion of the function to get the count. I noticed that…
Unparseable error sumifs with cross sheet reference
Hi team, I cannot get rid of the unparseable error for the following formula. I think the issue is the =[Month]@row and =[Country]@row. I have tried also without the brackets []. =SUMIFS({EMEA KPI Issues No of EE},{EMEA KPI Issues Category},"vendor missed data",{EMEA KPI Issues Country}, =[Country]@row,{EMEA KPI Issues…
Extract 10-digit string based on consistent lead-in digits
I have a number I'm trying to extract from a column and I can't figure out how. The number is *always* 10 digits, *always* starts with 50000, and can appear *anywhere* in the cell in Column A (start, middle or end). Column A has a mock-up of the type of data I'm working with. The Result column shows what I want to achieve:…

Formulas and Functions

Help Article Resources

Categories

Popular Tags

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags