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
Keeping count…in three different places
I hope I’m asking this question right…here we go. I’m attempting to create an inventory log where users can add or transfer items from and to one of three locations. The problem I’m running into is this: The amount of items that can be added or transferred between sites are numerous and I’m trying to think of the most…
How to use =SUM(CHILDREN()) for all PARENT in the same column ?
Hello i would like to know how i can SUM a budget in the first row of PARENT regarding their CHILDREN. And then make it a column formula but for just the PARENT rows.
Days to Launch, stop at Zero
I have created a formula to countdown the days to launch and converted it to a Column forumula so it is automatic for any project. However, we don't need to know the negative numbers or day past launch in this column. I tried nesting an IF function to return a blank cell for anything less than zero but I the dreaded…
Is there a formula that will count the number of days a box has been checked?
Have a sheet with checkbox column for when that row has a problem with it in an internal database. Is there a way to add a column that will update daily, counting the number of days the box has been checked? and even better, will stop counting once the box is unchecked? Wanting to measure how quickly problems in the…
Is there a way to create a list out of joined string in a cell?
I have Sheet A, were I have a column with Task Description that has parents. I created another column where I put the parents corresponding to the child. Example: Parent ID .........|........Task Description [blankcell]........|........Parent 1 [Parent 1].........|............Task 1 [Parent 1].........|............Task 2…
How to sum column totals for visible rows only?
Hello, I currently have a checkbox that allows a row to be filtered out (i.e. out of scope items). When these rows are filtered, I would like the associated numbers to not be included in the column sum totals. I tried searching but I must not be searching the right terms to find help on this function. Any help appreciated!…
Creating a report off of Automation settings within sheets
Is there a way to create a report in smartsheets to pull data off of the automation that's set up? For example, I have multiple sheets set up and I want to make sure the automation in each of them is set up the same. Instead of having to go into each smartsheet I was hoping I could create a report to pull that information…
Count Distinct Values in a Column
I have a serial # column that I am trying to create a "helper" column to verify that the serial # entered is unique - to avoid/flag duplicate entries with conditional formatting later. I have tried this formula in the blue highlighted cell pictured below: =COUNTM(DISTINCT(([Computer Serial # (Parent Asset)]:[Computer…
INDEX/Match Problem Across Sheets
Here is my problem: I'm trying to compare dates on sheet A to a list of dates on Sheet B and if the dates match, fill in a number on sheet A. If I move the data over to sheet A and use this formula works fine: =INDEX([Column9]:[Column9], MATCH([Column4]@row, [Column8]:[Column8], 0)) When I use a cross sheet reference I get…
Pulling specific Data from Project Intake
Hi I have a portfolio of projects and using the PMO template. I would like to pull the Target Start Date to my first activity in the respective project plan. What formula would I use to pull the specific project target Date for the Project Intake sheet?
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…