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.
Discussion List
Average for multiple 5 Star Rating Columns
Hi, I have about 8 columns with ratings, all columns have the 5 star rating option, how can I collect an average of all columns? Thank you! 😃
Formula for At Risk Flag
Hi Experts, I am using the following formula to turn the At Risk flag red when the condition is true. But I am getting a #UNPARSEABLE error. The formula is trying to say: If either of these conditions is true, I want to turn the At Risk flag red: When an End Date is within 3 days of today AND % complete is below 80% When…
Text to columns vs IF statement
I have daily report that contains a string of text (Reasons) regarding issues that occurred overnight. These are usually anywhere from 2 to 20 in each report, and while that is manageable on a day to day basis, I'm trying to pull in the last year's worth of data and that is entirely too much to parse by hand. I'd like to…
Formula issue
Hi Can someone help me to write these five formula in a single formula to get the schedule health. IF(Status@row = "Not Started", "Gray") IF(Status@row = "On Hold", "Yellow") IF(Status@row = "Not Applicable", "") IF(Act1@row >Act2@row , "Red") IF(Act1@row <Act2@row , "Green") THanks
Urgent help needed on 2 formulas
Hello, We had an incorrect data uploader workflow wipe out our entire sheet. I was able to recover the data from support, but still need to rebuild some of the formulas that did not come through (weren't column formulas). I have the basics of this one, but am missing something as it shows as unparseable. (1) We want this…
Formula not working
Hi , Can anyone help me with this formula issue:- I have 4 symbol color- Not Started- Gray Not Applicable- No color On Hold -Yellow Complete- Green Act1 greater than Act2 and Status is equal to In Progress - Red else Green =IF( Status@row <> "Not Applicable", IF( Status@row = "Not Started", "Gray", IF(Status@row = "On…
I need a formula that checks to see if all the assessments in a given area are compelted.
I want a formula to check and see that all the assessments for Anti-Corruption are completed and send an alert based on that.
Return Sprint based on finish date
I have a project schedule where I am tracking activities. I would like to return a sprint number in column "Sprint" based on the finish date. Example: Sprint 1 is 6/23 - 7/6 and the task ends 6/29 so I would like the column to return a value of "Q1 Sprint 1" I have set up a separate sheet with the Sprints and start/finish…
How remove a character ">" in order to SUM values in one column if they meet a criteria.
I need to be able to get the results using a formula. (I can't use a helper column, or the find and replace to remove ">". Logic needed: This formula should substitute the greater-than sign with nothing and convert those to numbers. Then it will need to sum the numbers where the criteria is met (in this case the condition…
if formula
Hi I was wondering if someone could assist. I'm trying to have a box check if the criteria on two sheets are the same. Example would: I have a column called Submission Number on one sheet and I have another sheet with a column with submission numbers. I would like if a number appears on both sheets I would like to check a…
Help Article Resources
Trending in Formulas and Functions
COUNTIFS formula for any future date?
Hi all, I am trying to create a COUNTIFS formula that counts if the due date column on another sheet is today or any future date. I tried =COUNTIFS({due date column's range}, >=TODAY()), but it is not working. Any advice would be appreciated! Thank you.
Sheet Summary Field - Count Value from Dropdown
What's wrong with this Formula? Getting an Invalid Ref
I am trying to get a unit count between a date range.
I am trying to get a unit count between a date range. I have two difference columns from one sheet and two difference columns on my main sheet I am referencing. I keep getting unparseable. Is there a way to to get the number I want with using the countifs formula? Or should I be using a different formula? This is the…