Formulas and Functions

Archives for July 2023

How to return a MAX Value of "0" if Remaining # of Weeks is < 0
Hello Amazing Community, I'm trying to create a formula to Remaining Number of Weeks on a project. Start Date = 5/30/23 End Date = 6/15/23 Formula for Remaining # of Weeks (from today) =NETDAYS(TODAY(), [End Date]@row) / 7 = -2.9 **Problem is when End Date is in the past, and TODAY keeps changing, in which case, Remaining…
Auto number formula
I have a master sheet that updates from another sheet that has an auto number column but I need an additional one. Is there a formula that I could use?
I want to parse words and get a count
Situation: I have open ended survey answers. I want to create blog content to address the most highly mentioned concerns. I need to go through the answers and find any word (i.e., I don't have any particular words in mind) and then get a count of the number of times that word occurs. (I expect I'll get the word 'the' 10…
Portfolio Formation
Dear Team, I have been building my Smartsheets and creating work flow. Now I want to build a PORTFOLIO Sheet to pull data from my Smartsheets. I have been using a template so far but I want to build my own. How do I form a PORTFOLIO Sheet? How to I link it to my files/folders? How to I get it to count a category? So for…
Help with Conditional Formatting
I have to create a report that has conditional formatting onto it. I am not really sure how to work Smartsheet's conditional formatting stuff. The example I was given to create was: "Can color be added based on calendar logic (0-30 days since xxx, for example)" There is an active sheet that we would use to put the…
Match Contact on a sheet from imported data
I have an import file with a column that contain "Last Name, First Name" and I'd like to update with my Smartsheet Contact. Has anyone done this? Brent
Calculate Assigned Date Based on Drop Down Column Values and Due Date
In my Smartsheet I have a "Severity" drop down column with the following options: Critical, High, Medium, Low. There is a "Due Date" column that is filled in. I need a formula to populate an "Assigned Date" column as follows: -If "Severity" is "Critical" or "High", "Assigned Date" will be 30 calendar days prior to "Due…
Count to show the High Priority Items
I would like to add a formula that counts the number of 1 - High, 2 - Med, 3 - Low in each row.
Formula help needed: multiple combinations
Hello, I am in need of help with a formula. Basically, I have a formula looking at column A. Column A has 9 drop-down options, multi-select. Each of the 9 options has a specific (i.e., different) email associated with them. I have a large IF function that will accurately give the appropriate email, based on a single…
Convert Date to Julian Date
I am trying to convert a date to a Julian date, DDDYY or YYDDD. I have done this in excel before, but I am not having much luck in Smartsheet

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

Watch video

Trending in Formulas and Functions

Revise Formula to include a but
My current formula in one column that is working out is the below: =IF(OR(CONTAINS("Term", [Column1]@row), [Column2]@row = 1), "Green", "Yellow") For the next column I need a revised formula to say "if the term is in column 1 and column 2 is check BUT Column 3 is checked then it will give me a red ball" What do I need to…
Can I create one workflow to notify multiple conditional paths or does each condition need it's own?
Hello, I am trying to create a workflow where specific people at a given project are notified when a request is fulfilled. I have set it up as it appears in the included picture but I am only receiving alerts when the first project is selected. Do I have to create a separate workflow for each of the projects or is there a…
I need to change a column named Event Type to a "V" or "I" based on the value of another column.
I need to change the Event Type column to a "V" or "I" if a column named Event location contain the word "Virtual" or "virtual". I have tried several versions of IF statements but they all give errors. Below is the last version I tried. =IF([Event Location]@row, "Virtual", "V", IF([Event Location]@row, "virtual"), "V",…

Formulas and Functions

Discussion List

Help Article Resources