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 do I extract dateonly from system generated date columns?
Hello, I would like to create conditional formatting based on a system-generated date being more than 7 days in the past (my test is if it's less than 6, then trigger the flag). I created a helper column to do the math, upon which the conditional formatting is supposed to be set (if the flag is activated, then turn…
Formatting based on child row
Is there a way to update the parent row based on the child row? Every time a new product goes out for testing, it is added to my sheet as a parent row. If that product needs to be retested, I am adding it as a child. If the child passes testing, is there a way to update the results in the parent row?
Text/String Version of Max formula
I have a sheet where I am writing rows and not all entries will update each column. I want to reference that column on another sheet and only return the value that is not blank. I can't quite get the ISBLANK formula to work for this. I've done this with dates using =MAX(range). Not sure how to accomplish with text. Thanks…
Index MATCH vs COLLECT (matching 2 values, returning a different value)
Hello all, I need to look up 2 values on a separate sheet and return a value from another column. I keep getting the "#INCORRECT ARGUMENT SET" Basically I want to get a % complete back based on 2 value matches (brand) and (task name). The task name is duplicated across brands, but the brand never has the task name twice.…
Cross cell formula
I am trying to create a formula that will represent the estimated hours per task for all resources allocated for that task Currently: Duration is in days "Allocation % is 5% for the duration of the task "Estimated Work Hrs": =((Duration11 * 8) * [Allocation %]11) 8 is reflective of hrs in a day Since I have more than one…
Creating a project kick off date based on go live dates
I have a project tracker where I'd like to be able to predict project kick off dates based on when projects go live. It ideally would find the minimum FPFV (Updated) date and calculate the project kick off date as the max actual go live date + 1 week. Any thoughts on how best to do this?
If 2 criteria are met in 2 columns then sum a third column formula
Hi All, I had such great luck with help yesterday, I thought I would try again. I need to sum a column if 2 other column with specific criteria are met. This is the formula I have tried but it is not correct. I get UNPARSEABLE error. =IFS[Vendor Name]:[Vendor Name], "Curium", [Claim Status]:[Claim Status], "Pending - With…
Can you change these labels?
Would like the blue and red to have a different label. TIA
COUNTIFS with three criteria
This discussion was created from comments split from: COUNTIFS Referencing Other Sheets.
How to perform trend analysis on symbols
I have a sheet where I have two columns that show status (current and previous). These are represented by the colored dots (Red, Yellow, Green, Gray). I am trying to show tend on these status items with the following logic: =IF([Trend - Last Report status]1 = [Overall Status]1, "Sideways", IF([Trend - Last Report status]1…

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

Using JOIN/COLLECT and an OR?
I'm trying to write a formula using the JOIN and COLLECT options. However, I've hit a sticky point when I want to include an OR criterion on the COLLECT portion. My current formula (which works perfectly) is: =JOIN(COLLECT({Assets Range 1}, {Assets Range 2}, [Tool]@row, {Assets Range 3}, "Live"), ", ") However, I want to…
INDEX/COLLECT with both MATCH and CONTAINS criteria
I am looking for help with a cross-sheet INDEX/COLLECT formula. I need to MATCH in one criteria and CONTAINS in another criteria. I can get them working separately but I don't know how to combine into a working formula. Source Sheet: Destination Sheet: In the Attending School column in Destination Sheet, I'd like to…
Parent Color Symbol, Based on "Average" of Child Symbols
Okay, here I am again! On the Project I am working on, the Stakeholder wants parent rows for no reason other than to be able to expand and collapse the data. I nicely suggested that since the data would only be for 1 year (broken down in quarters and about 5 users listed each quarter), that since the data was not excessive…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags