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
Counting no. of times a value is entered in a column
I'm using this formula and the answer comes up 0. I want to count the no. of times 0 and 1 appear in a column. =COUNTIFS([No. of mos. cash available]:[No. of mos. cash available], AND(CONTAINS("0", @cell), CONTAINS("1", @cell)))
IF statement for a check box helper column
I have a form that has a status of sick or returning. There are other columns but not needed for the formula (other than name). I want to add a helper checkbox and when someone submits a form for returning (they will have already submitted one for sick) the matching "sick" row and the new returning row will match the name…
Nested IF for symbols health status
Good Day! I am having an error message #UNPARSABLE. I am trying to create a formula to automatically change the health status project tasks. Our project management life cycle is very simple, we track only 6 milestones. Thus, I would like to compare all 6 milestones and provide the overall health status R-G-Y according to…
Need help
Good Day! I need help to fix the formula below. I am having this error message: Incorrect Argument Set. Thanks in advance =IF(Phase@row; "Analyse/Conception"; IF([Date Requise DC]@row - TODAY() <= 10; "Rouge"; IF([Date Requise DC]@row - TODAY() > 20; "Vert"; "Jaune")); IF(Phase@row; "Exécution"; IF([Date Requise MES]@row -…
Countifs Function Not working
So, I've been working on this for a day. I have a headache lol. Can someone take a look at this formula and make suggestion as to what I'm missing? =COUNTIFS({Staff}, "Name", ({Event Type}, "Sale") I'm using 2 columns of data and want to do a count using Name and type of sale condition. Suggestion?
Trying to count values based on variables
Hi there - first question ever! Also - not great at using the right language so sorry if it's a bit confusing! I am currently trying to use a formula to count if a certain condition is met in column a - and in column b - to count it. so for example Column A (Role) Manager Assistant Officer Column B (Off Work) Yes No Yes So…
Return Same Vlookup for certain day.
Basically what im trying to do is a project tracker. When the guys do a project they spend a certain amount of hours on it. thats all good until the project is paused and restarted again i have a issue where it calculates off the closest date. So what I tried to do was if the project start was on the same day as the finish…
How To Compare Dates
Why does this: =if([End Date]7=TODAY,"Yes","no") return #UNPARSEABLE? And this: -if(and([End Date]7<TODAY, [%Complete]<100),"RED", "YELLOW")
Can a formula be entered into a date field and get it to stick
I have a sheet that tracks consultations. These are normally each row represents a single consultation. But occasionally we have a workshop where it takes several row to capture the consultations. We are using child / parent relationships to capture the rows related to the workshop. We use the top row as the parent and…
To change the value of another cell
I've searched this subject for a couple days now and I have seen this asked a few times but with their own request with specific technicalities like percentage of completion and such. Many answers include workflow or changing the cell in question to a formula which don't appear to be the answer here. My interest in this is…
Help Article Resources
Trending in Formulas and Functions
Nested INDEX/MATCH formula for conditional cross-sheet reference use case
I am currently using the following INDEX/MATCH formula to return values from the sheet labeled "Tech Project List" in the reference. =INDEX({Summary from Tech Project List}, MATCH([Tech List Row ID]@row, {Project ID from Tech Project List}, 0)) I now want to expand that formula to include a condition: If #NO MATCH on Tech…
Sum cells based on multiple criteria with a timeframe requirement
I am working on trying to sum the time I spend across all my projects and want to sum different columns but only when they meet different criteria. Every formula I work through doesn't seems to work. I want to sum the Duration of Meeting IF the start date of a subject is within 5 days of the date in the green box that I…
Formula to populate field from another sheet using multiple criteria
Hi there, In one sheet ("pay scale") I have our employee pay scale and in another ("Employee Salary") I have our employees listed with their experience and education levels. Is there a Smartsheet formula that I can use to pull in their salary based on their experience and education levels? For example, if they have a BA +…