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

How to pull the top parent to the row
I need a way to consistently pull the top-level parent to all rows below for our reports. Here's an example: In this case, all of the apples need to have "Alpha" on their row. Similarly, all of the variations of Ed, Edd, and Eddie, as well as Easy and Edison, need to have "Echo" on their row. So on and so forth. Right now,…
what is the best way to combine contacts from children rows into parent row?
In a contacts column, I would like to combine email addresses that are in 2 children rows into their parent row. I have a helper column that checks a box of the children rows using the formula: =IF(COUNT(ANCESTORS([Protocol ID]@row)) > 0, 1)
Checkbox only at parent level
I want to add a DONE column with a checkbox but I only want the checkbox to be visible at the parent level not at the child level. I have a helper column created but how do I hide the checkboxes at the child level? There is actually quite a bit of info that I only want to track at the parent level the child info is…
Why has automation notified me now instead of 30 days before
Below is extract from sheet as you can see the date for expiry is July for employee Karl N. Automation is set to run 30 days before that date but I have received a notification today 28/04 not 30 days before. Any ideas why?
Formula to return action if / in field, copy cell if no /
I have a Credit Line column that is set up to either have an individual name or an individual name/company. I am pulling this into a column that removes the company names and just returns the individual names. This formula is working for that purpose: =IF(CONTAINS("/", [Credit Line]@row), LEFT([Credit Line]@row, FIND("/",…
Identifying calculations based on a unique property code.
I am working on a project that evaluates incoming photos for a brand.com website. We approve, reject or correct photos. We track each submission by property code on a daily tracking sheet. At the end of the business day, those entries are automatically moved to an archive sheet. Variables: There could be more than one…
What formula to use to link status changes in two different sheets
Hello, I want to link 2 cells in different sheets so that when I change the Status of one particular row to "Complete", it will automatically change the Status in another sheet to "Approved". I have been trying to use the INDEX(MATCH function but have not been successful yet. I'm hoping someone can give me some help as to…
Help with # of days Formula. Returning "#UNPARSEABLE" Error
I am trying to calculate the number of days between two date columns in my Smartsheet: "Date" and "Date Closed". If "Date Closed" is blank (indicating the issue is still open), I want to calculate the number of days from "Date" to the current date. Here is the formula I am using: =IF(ISBLANK([Date Closed]@row), TODAY() -…
Issue with task health formula
Hi all, I'm defining a column that will help PM's easily see which rows in a very large timeline to focus on (Health is the column name). I typed this directly into SmartSheet to avoid any invisible characters. I've looked at it many times and can't see an issue with the formula. Can anyone else see it? Based on columns…
Count If one column meets two criteria
I am trying to adjust some formulas we have for budgeting replacement computers. The sheet references another sheet and previously was just counting based on a date range to count computers due for replacement. I now wanted to expand this out further and use it to count Windows replacements and Mac replacements. I need the…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Popular Tags

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Multiple IF Statements Using "ISDATE"
I have an existing formula that enters text based on two criteria: if a date exists in a column, and if that item was closed prior to the anticipated delivery date. The current formula is: =IF(ISDATE([Closed Date]1), IF([Delivery Date]1 > [Closed Date]1, "Complete", "Fail")) I'd like to add an additional criterion that…
Formula
I am trying to identify how many surveys taken at the start of the program are from the Jacksonville market using this formula =COUNTIFS({Time of Survey}, {Time of Survey}, "Week 1 of program", {Data}, "FLJAC Jacksonville") but it retuns as Incorrect # of arguments
Conditional formatting to help input mask/ensure social security has 9 digits
I've labored over trying to figure how to get around how SS gets input and for it to highlit if it doesn't contain 9 digits and hyphens. …as I'm typing this, I guess I could do it the same way I did my zip code and just leave out hyphens and have them inected into another column…. I'll work on that until I see someone has…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags