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!

Trending in Formulas and Functions

Record a date automation isn't working
I set up the automation below, and it's not working. When I change the status of a row to complete, nothing happens. If I go into the automation manager and select "run" or "test" the completion date column fills in with today's date. What am I doing wrong? This should be straight-forward. I've tried building this…
Identifying oldest entry on sheet, for different groups
Hello! I am working on a "waitlist" process, where the sheet will have multiple candidates under each work location. I need a formula to identify the oldest entry for each location, and if there are multiple entries for the earliest date, use a "score" column identify the higher score as the next candidate in line. Example…
Week Over Week Change
I have a sheet that has orders entries from the last 6 months. This sheet continues to populate as more orders are made. This is just my repository sheet. I am looking to get the week over week change metrics for the last 7 days. Here's the formula that can provide me that week over week change: Week over week %% =…

Formulas and Functions

Discussion List

Help Article Resources