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.

If Then with Multiple Ifs
I have two columns that I need to check if "true" before returning the text in the 3rd column. I am trying to create a formula that returns the Awarded Contractor's name in each City, i.e. Contractor Y for Boise and Contractor A for Chicago. I have tried a number of different IF, IFS, IF(AND) statements and can't figure it…
Join Collect formula to list Trucks Rented over 6 months
Hello, I need to create a list of vehicles that have been rented longer than 6 months. Below is the formula I am using. It is returning Incorrect Argument set. =JOIN(COLLECT([VIN Number / Número VIN]:[VIN Number / Número VIN], [Date Rented]:[Date Rented], TODAY(-132), CHAR(10)))
Formula and functions
How can I keep this with the wording On track, Off track, at risk and assign a color to it like green, red, and yellow? Then pulled from the sheet to a dashboard to reflect those words and colors?
Automation of responses from one form onto a second form?
I'm developing a skills competency checklist using 2 forms to collect info (1) self-assessment for staff to check-off skills and (2) Validation/Assessment from preceptor to check and sign-off that skills were validated. Is there a way to set up logic that auto populates responses from the first form onto the second form so…
Network Day giving negative number for the same day
We have this formula to give us how many days are left for the Purchase order =-NETWORKDAY([Due Date: PO Ready]1150, TODAY()) But whenever I have today selected I get a negative -1 as Day Remaining Same happens when I select day after tommorow just gives me 2 as Days remaining How can I fix this? When the days are more or…
Find unique value in Children
I am looking to determine if a value in a cell of a child is unique among the other children. Order Number | Items | Cost | Overall Unique | Order Unique | Total + 100 | | | 101 | Oranges | $1.00 | Yes | Yes | $1.00 102 | Apples | $2.00 | Yes | Yes | $2.00 103 | Bananas | $3.00 | Yes | Yes | $3.00 + 200 | | 204 | Pears |…
Issue with nested IF formula looking at the 1st column THEN 2nd column (to feed INDEX/MATCH data)
I am having an issue with this formula and need help :) I am wanting the formula to look at the "Lead CTC Region" column for Canada first (and pull in the "Lead AE" data with the INDEX/MATCH piece) THEN I want the formula to look at the "Lead CTC Global Region" column (and pull in the "Lead AE" data with the INDEX/MATCH…
VLOOKUP Not working
I have a sheet of all our products for sale. We are introducing new pricing in Jan. 2024. I have the new pricing in a smaller sheet that I imported in from Excel. The first column of that sheet is our Core Stock Number, then the pricing column, for example, Retail, Sale, Qty Discounts, etc. I want to use a VLOOKUP to put…
Not counting based on multiple factors
I'm building a formula that uses the contents in a cell to effect a percentage. Basically: =(count all cells within a range with the word "PASS") (divide this number by) (All the cells within same range that are NOT blank ) The piece of the formula that looks at the row and avoids blank cells looks like this:…
How to fix #UNPARSEABLE for my nested IF formula
Here is my nested IF formula and I don't understand where the issue lies. I've created a new column called [Initiative Expense] for my formula. My [Initiative Type] column has multiple values for types of expenses and credits, but if the value is one of five expenses (listed in parenthesis in the formula), then I want the…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

Date formula help
Hi, I have a start date column and a complete date column. I am trying to track number of days since the start date (to today) that stops counting when the complete date column is filled in. If I use NETDAYS, it does not give me a count until the second date is filled in. Thanks!
Text Join Formula
Hi there, I created this formula: =JOIN(Course@row) + " " + [Course Date]@row that worked but I need assistance on changing the order of it. Course Date with Parentheses around the date and Couse Name Formula that I have: =JOIN(" ", "(" + [Course Date]@row + ")", [Course]@row) that will not work Example what I want:…
Formula won't recognize cell data
Hi, I have several formulas in sheet summary that reference a single person in an assignment column in my sheet. The formulas are repeated multiple times and work just fine, however I seem to have issues with this single person. Any formula that includes this person, does not work. Any insight on how to fix this? I've…

Formulas and Functions

Help Article Resources

Categories

Popular Tags

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags