Formulas and Functions

Archives for July 2019

Formula for RYG symbols with dates and checkbox
I'm trying to combine two formulas to automate RYG symbols. The first, this one, works fine to automate the symbols to be either red or yellow. Row #1 in the screenshot. The purpose is to keep the symbol red if the date column is either blank or empty, and yellow if there is a date in the column. =IF(ISBLANK([Start…
Calculate End Date from "Start Date Plus Number of Days (calculated and rounded up via formula)"
Hello Community! I'm trying to calculate End dates based off of a hard Start date, and adding a number of Days that I have listed in another cell. The number of Days is calculated via formula, from one cell that lists Project hours (which I manually input) and each project is assigned to a crew, which have different…
Harvey Progress Ball at Parent Level
I have set up a column ("Task Complete Score") for numbering (0-4) to show progress which I am using to automate a column ("Progress") showing Harvey balls. I’d like to add a Harvey Ball for the parent row using an easy average. At the parent level, I have added this formula AVG(CHILDREN()) to get the average "Task…
How to exclude values from a COUNTIF expression
I'm writing a COUNTIF formula referencing a column with multiple values that works well, until I try to build an expression that says: I want you to count the values in this column as long as they are NOT "x" OR "y" OR "z". Can anyone help with the proper syntax? I've tried the following but it doesn't work: COUNTIF…
Formula to Add Days to Calendar Date
Good Morning! So I was wondering if it would be possible to have a formula that would add 6 months (183 days) to a cell with a prepopulated date if a certain criteria is met. So managers will get a form and they will pick either "Yes" or No" for "Extend Vendor" and if they say "Yes" I want it to add 6 months to "New…
Month Formula
Having some issues with the month formula popping up errors when being used as a reference for a range in the collect and countif/countifs functions The below formula produces an error =JOIN(COLLECT(Date:Date, Date:Date, MONTH(@cell) = 1)) The below formula returns a 1 even though every day in january is included in the…
Use DISTINCT with COUNTIFS
Hey Guys! I am so excited to see that Smartsheet has finally introduced a formula that will compute DISTINCT(Unique) values!! So, I am trying ot develop a formula to calculate DISTINCT count using one column but with a few condition(s) i.e. IFS i.e. [Range1,Criteria1, Range2,Criteria2] Refer below a sample of that…
Self-updating report based on sheets in folder
Hi there, Is there a way that I can build a report so that when a new sheet is added to a smartsheet folder, the contents of the sheet then automatically gets compiled into a report? I saw that it was possible to select a folder when building a report, which I was really hoping would have that functionality. But it looks…
How to select a last added value from a column
Would love to get feedback on how to get a "Last added value" of a particular item. I have a list of items which keeps adding in the sheet as new rows. I need a formula that looks at a column to pull the latest value/entry made. What I want is a formula that updates automatically as I populate new rows. Below is a…
IF FORMULA TO PUT AUTOMATIC STATUS
I would like to know what can I do to put automatic status for my monitoring sheet? There are 3 status: In Transit, Cancelled and Delivered. I want to know if its possible that when the requestor input the delivery date (refer to the screenshot), the status will change to "Delivered". For the Cancelled and Transit though,…

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

IndexCollect / JoinCollect for Multi Select Columns
I have one column called PRIMARY PRODUCT which is a single select dropdown and another called PRODUCTS OTHER which is a multi-select drop down. I have a third column called PROFIT CENTRE CODE which needs to populate based on all the products selected in the products columns. I have worked out a formula for PRODUCTS OTHER…
Count Active assignments by individual
=COUNTIFS([Assigned To]:[Assigned To], ("Name"))AND [Request Status];[Request Status], is one of "In Progress", "Submitted", "Completed") My countifs statement works by name but I want to further refine this by [Request Status];[Request Status], is one of "In Progress", "Submitted", "Completed")
If then formula help
Hi All! I have a column , Average Score, that has been calculated using other fields. I want to use the average score to determine if an initiative is a program or project. If the initiative’s average score is between: 5 and 9 it is considered a project and if the score is between 10 and 15, it is considered a program. I…

Formulas and Functions

Discussion List

Help Article Resources