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
RYG - AHHHHH!!!!
I CANT FIGURE THIS OUT!!! - SEND HELP! I have a "At Risk" Column for this formula I have a start date / finish date / % complete column this is what I am trying to automate Past the due date and not 100% complete (RED). Less than 60% complete for tasks coming due within 5 business days (YELLOW). Above 60% complete for…
Automate EOM Entry
Is there a way in Smartsheet to automatically look at a month, and return the end of that month (as a number)? Thanks, Dennis
CountIf with Date ranges
Trying to write a formula that counts the number of instances based on a date range. I can get the instances to populate in a formula by themselves but I cannot get the date range to work? This is weird as the formula works in another sheet for another scenario. =COUNTIFS({CURRENT CHECKOUT SCENARIO Range 1}, [Primary…
Need an Formula to Lookup and get Multiple ID's as per Countries in column two
I need to write a formula in the column ID's to bring all the ID's associated with country list like output example column (Green color in screenshot). We have multiple countries in the Country column but still it should consider the ID. Like Lookup to get multiple values as per criteria. Someone could you please help me…
Formula to check box if oldest milestone?
Hi there, I have a set of project plans that I wish to summarize in one report, and in that report I only want to know the oldest (aka soonest) milestone that is not completed for each project. To get to that, I figure I will have a checkbox column in every project plan, with a formula that checks the box if it is a…
Formula for counting task status by Sub team
Hi, I want to calculate the number of 'Complete', 'In Progress' tasks by each sub team. The Sub Team column is a multiple value drop down. Need help with formula to calculate. Thanks in advance!
Averageif and match for summary of info
I'm trying to average the the percent of submissions for an assignment on one Smartsheet based on a teachers ID in the current sheet. Here's the formula I've tried =AVERAGEIF({Quiz 1 Completion Avg}, MATCH(ID@row, {ID })) The results that are populating in my sheet do not match with are not correct. We have one sheet (the…
Populating a column in one sheet from a column in another sheet
I have 2 sheets, Techs and Totals. I want to: Populate a column called Tech/RSM List in the Totals sheet from the column called Tech Name in the Tech sheet. I guess the question is, how do I just reference the row in an Index formula. If I do: =INDEX({Tech/RSM List Range 1}, 1) I will get a valid return. If I do this:…
Formula to flag overdue Items with 2 conditions
I am currently using this formula to indicate if a task is overdue (via a flag). =IF(AND(NOT(ISBLANK([End Date]@row)), [End Date]@row < TODAY()), 1, 0) I just realized that the Overdue flag is still red when the Status is 'Complete.' How do I add this into my formula? Tried to add NOT(Status@row= "Complete") but I'm either…
Help with formula with multiple conditions to auto-update RYG ball
I am trying to create a formula with multiple conditions in order to automatically change the color of the RYG ball. IF the Status = Not Started/In Progress/Blank AND the End Date is less than TODAY () then the ball should be Red OR IF the Status = Not Started/In Progress/Blank AND the End Date is in the next 7 days then…
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…