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
Using dates in a formula for text/number columns
I have text/number columns that represent weeks in a project timeline template. I want to be able to keep the column names generic (Week 1, Week 2, etc) and have the Monday date of each week in Row 1. Projects last months to over a year, so manually typing these dates in is tedious. The ideal scenario would be to put in…
MATCH bug?
What is the ideal formula to derive numeric outcome with multiple conditions
Hello All, I am trying to working on solution which has multiple conditions with final outcome as numeric. I have tried with IF formula with multiple conditions wherein if the first condition is "X" then it should be "Invoice value * 10" and if second condition is "Y" then it should be "Invoice value * 20"with outcome as…
Auto Generate a Number based on Column not being blank.
Hello, I am trying to get an auto generated number in Auto Number column. I cannot use auto number given that i have conditions to it. I want my Autonumber column to have a formula where it will create a sequential number based on the Hold Issuance date not being blank. I only want it to autonumber if the hold issuance…
IF OR/AND Function - Considering Dates (Future, Past etc)
Hi Team - I have a bit of a doozy.. at least it is for me. I have a sheet that needs a status populated considering 2 different date columns. If either "Expiry Date" or "Expiry Date 2" is in the past we need a "Red" status If either "Expiry Date" or "Expiry Date 2" columns are within the next 30 days "Yellow" Status If…
Template Dashboard w Form Filter
Hi all, I am attempting to use this dashboard template, Note this includes the dummy data that comes with the template and I will be replacing it with my own info. There appear to be four primary sheets, noted below. Intake Sheet Region Sheet - this is where helper columns are. It looks like the region info feeds into the…
Smartsheet Sheet Summary
I'm new to smart sheet, but in playing around with building a jobsite estimates… is there a way to show a subcontractor within a scope vertically in a column? The attached screen shot shows work scope for concrete flatwork, and rather than stack the subs I'd like to look at them side by side across the board which would…
Negative Float
Can anyone tell me what would have to change in the plan below for me to end up with a negative float? It seems that if I add any duration to Activities B through L (which I would expect would create a negative float), the end date for the final task (Activity M) simply gets pushed out to compensate. No Negative Float…
Formula with symbols when a cell is NOT blank
HI Everyone, I´m looking for help with the following situation. I want My "Closed within due date" column to show: Gray when the case has not been closed yet (and therefore Closed date is blank) Green when Closed date is equal or less than Due date Red when Closed date is higher than Due date. I cant figure out the par for…
Formula to perform calculation using parameter associated with most recent date?
I'm trying to generate a column formula that will calculate 'Dose' using 'Concentration' and 'Purity' for each row, but only using the 'Weight' associated with the most recent 'Collection Date' grouped by 'Subject ID'. For the example below, the Dose for Subject A (Test #2) should be: Dose (A, Test #2) = (1.3 * 0.95)/(26)…
Help Article Resources
Trending in Formulas and Functions
Need to find all instances of value and return any non-blank cell from another sheet?
Hello Everyone, I'm stumped and need some assistance. What I am trying to do is look for the value in the Cable Number column (Cable Number]@row) on the master sheet in the Cable Number Column on the Update Tracker (Update Tracker Cable}). There will be multiple times this value is in this column but some dates will be…
Hello I am trying to create a formula that returns a specific date when a dropdown selection is made
class requested start date Jan 13, 2025 - July 14, 2025 Oconomowoc, WI Jan 13, 2025 - July 11, 2025 Huntsville, AL (new site) Feb 10, 2025 - Aug 11, 2025 Amsterdam, NY Mar 24, 2025 - Sept 22, 2025 Fontana, CA Apr 21, 2025 - Oct 20, 2025 Oconomowoc, WI May 13, 2025 - Nov 17, 2025 Huntsville, AL Jun 16, 2025 - Dec 15, 2025…
How Do I add Sum(Children) at the bottom of the children rows
Hi, How do I use the =Sum(Children) function in Smartsheet to sum child rows and display the total at the bottom. I usually create a parent row, then add rows beneath it, indent them and add the formula =Sum(Children) to the parent row to calculate the total. However, that structure was confusing for users, and they…