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
FORMULA TO COUNT ITEMS NOT COMPLETE BY PARENT ROW
I have 5 main parents with some smaller indents. Pre-Explore (one of the 5 main sections) within that I have a parent meetings with children and several other parents with no children. I have a % complete column we are manually filling out. I'd like to be able to show just the 5 main sections and provide a number of total…
INDEX MATCH working on one sheet but not another
Hi I have set up an example sheet with an index match formula that looks up an invoice to see if it appears on another sheet. If it doesn't it will bring back the value "New". This formula is working on the example sheet but when it is copied to the master sheet, it doesn't work . See screen shot: Example sheet: Master…
Mark an old submission
Hi, I have a sheet I created where a name and status are being inputted through a Smarthseet form. I am trying to figure out a formula to mark old submissions. Meaning if someone submits a name with the status of "Onboarding" and then submits the form again with the status "Hired", what formula can I use to mark the old…
Count the amount of Failing scores across a range of columns
I am trying to count the amount of Failing scores across a range of columns based on the customer that is selected. Currently I am able to count the amount of Failing scores for Atlanta from 1 column with this function: =COUNTIFS({ORA: Flight Observation 1 Customer}, ="Atlanta", {ORA: Flight Observation 1 Q1 Score}, ="1.…
Receive approval over email for Approval workflow
Hi Community, I have built and approval workflow and can see it working on the sheet, app and by clicking the link in the email. I have been asked by the approvers to also make it possible to just reply to the email with "Approved" to log the approval but can see how you can make this work. Is approvals different to…
Pulling data from Project Plan into Project Intake
As the summary of all Project information, is it possible to pull the start date from a particular project folder; noting there are many?
How to connect Countifs with Jerarquies (Children?) with two conditions ( 100% & Dates )
Hello All, I am running this formula "=COUNTIFS([P%]:[P%], =1, [Finish date]:[Finish date], ISDATE(@cell))", And the result is "7", and that's correct!. But now I'm attempting to run a similar formula just for a specific cell "Abstract" and " Summary". Basically, I need the result for "Abstract" be "3" and for "Summary"…
A subtraction
I am getting an error message #ParSEABLE with the formula below, the values are coming from the below link as you can see in the second snapshot
Lookup - if - and?
Hi, We have a basic sheet using a form, users scan the items QR code, drop their location and select if checking in or out an asset (tick box) and nominate it's return date. Currently we have 100+ assets on the sheet. I would like the sheet to alert the Proj Manager when the item has not been returned by the due date.…
Symbol in formula
I am trying to tie a form sheet into another sheet that has a check mark. I want to set up a range on the form sheet to change the arrow symbol on the data sheet. Example: on the form sheet there is a column that I have linked to the data sheet this column is an average of the score from the form "Summary Score". in the…
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…