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.

Formulas and Functions

Stumped by a formula? Ask for help or find examples to see how others use a formula to solve a similar problem.

Count Criteria But Not Blanks
Hello! I am trying to count if a number is less than 45 but leave out the blanks. I have the first part of the formula working, just having trouble with the "do not count if blank" part. Can anyone help? =IF([Days to Complete Calc]@row < 45, 1) Thank you!
Exceeding 100% Completion
How do I override the system to account for more than 100% Completion. For instance, the initial project accounted for 4 visits; and when the project was completed the client wanted an additional event. When I add that event the Completion still adds up to 100% and not to 125% to show that the project went over scope.
TRIMMEAN
How does Smartsheet calculate TRIMMEAD (Excel)? Calculating the mean (AVG) taken by excluding a percentage of data points from the top and bottom tails of a data set. Thank you so much.
Count of Issues by Month/Year
Hi All I want to add to my summary sheet a count of instances by month and I'm not sure what I'm doing (only 4 hours into using it!) My source sheet has the data formatted UK (dd/mm/yy) and I want to have the summary sheet show mm/yy with the count of instances that occurred within the respective month and year Any…
Formula to assign colored dot symbol based on field?
Hello all. I am trying to utilize the colored symbol dots (green, red, blue, etc) while also having a text field specifying what the status of an item is. In my case, I am looking to have the following: Column A (Status) ------ Column B (Status Dot) Available ------ Green Pending ------ Yellow Unavailable ------ Red I want…
Workflow email content based on conditions/critieria
Hi All I'm very very new to SS (just got a license today) and I've been included in a team to improve the notification process to our wider audience with key information to help them complete actions quicker with minimum TLDR! Is it possible to take the column header and add the text to the output notification (email) if a…
How do I set dates depending on another task being completed?
If we speak with someone that completed a questionnaire we will follow up with them in 28,35,42 days. I am able to set the dates to be prefilled but how do I set the condition that if only the questionnaire was completed or is "green" we should enter the follow up dates? We have a column to show green or red for…
Counting Multiple Values in One Cell
Hello, We have a form with a multi-select option for "Event Type". I am trying to create a new sheet that can be used within a dashboard with a chart that shows how many "Event Types" there are within a given "Delivery Format". Snippet of sheet collecting form submissions below. The formula I was using to try and find the…
How to Record Data Weekly
Hi, I now have 1 main sheet, and then 4 different sheets where it moves over data to, 4 times throughout the month, using automation to move certain rows by month on the I.e. 7th etc. then I have a dashboard showing the different data of the 4 sheets, so you see 4 times throughout the month how the numbers changed. Is…
How to combine multiple IF statements into one?
Hello! I have 3 different IF statements in 3 separate columns that I would like combined into 1 if possible. The three IF statements/columns are the following: Red Status - if task is in the past and not checked complete: =IF(AND([End Date]@row < [Today Helper]@row, [Task Complete?]@row = 0), "Red", "Green")=IF(AND([End…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

If Date is blank + past formula
I can't figure out why this would return a "yes" when the date cell is blank. =IF([Date of Site Survey (Product)]@row = " ", " ", IF(TODAY() > [Date of Site Survey (Product)]@row, "yes", " ")) Intended logic/result - If date cell is blank, then "blank" (this worked when I tried without the 2nd argument added) If date is in…
Modifying a date based on 2 variables
Hello Everyone! I would like to create a formula that says If the value in the "Variable" column is "HIGH" AND the date in "Column 1" has a year less than today's year, return that date with TODAY"S year. Otherwise return that date unchanged. In other words, dates in the previous years are brought to this year, dates in…
How to make a manual Row ID based on conditions?
I'm not even sure if this is possible but I invite all the formula wizards to take a crack at it! I have this intake sheet with 10 rows, all that have a corresponding auto-generated Row ID from the system column. Row 1 is 23-001, row 2 is 23-002, row 3 is 23-003, etc. When a project gets approved, I want another column,…

Formulas and Functions

Help Article Resources

Categories

Popular Tags

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags