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
IF Formula using Dropdowns and Checkboxes
Hi, Smartsheet formula novice here! I hope the following will make sense. I'm attempting to create an IF formula that makes use of two drop down options from 1 column as well as checkboxes from 2 additional columns. Currently, the below 3 columns must be checked for the 4th column to be checked (starred) off. =IF([Entered…
Someone enters information in sheet 1 auto create row in sheet 2
Hi, I have two sheets: “De-install sheet” and “Inventory sheet”. Is it possible to automatically create a new row in the “De-install sheet” when someone enters information (date, serial# etc…) and selects “De-Installed” from the drop-down list in the “Inventory Sheet”. I would also like the new row to be populated with the…
Status based on Actual VS Targeted Dates
I need to have a formula for my Completion Status column. Here are the criteria and the screenshots. I keep failing at trying to write this formula, so I would appreciate some assistance. thanks in advance for your help!! The formula and all cells referenced are on the SAME sheet The formula is being written in the…
Sum of values excluding rows with duplicate
I need to sum the values in COL B while excluding all but the first instance of the corresponding criteria in COL A, which may contain duplicate values. Example: The result should be 19.
Counting dates 180 days from today's date
I would like to count the number of absences L6:K6 between today's date and the last 180 dates then multiply it by 2 plus add the number of late arrivals/early exits L6:AC6 between today's date and the last 180 dates. I tried the formula below in excel and it works correctly but can't get it to work in smartsheets:…
Referencing 2 sheets with different outcomes
Trying to refence 2 sheets and after each sheet reference bring back either Approved, Not Approved, and if not on either bring back Not Found. I'm using an IFERROR function but can figure out how to bring back the verbiage I want. Here is the fx I'm using: =IFERROR(MATCH([Requestor ]@row, {Smartsheet Whitelisted Domain…
Cross-sheet formula help required
I am using the formula from Smartsheet template Metadata sheet to capture data from intake sheet. However, one of the column doesn't seem to work, anyone has any idea what went wrong? This is the Metadata sheet, the data in red is incorrect. I am using this formula across from "Created by" to "Description":…
Syntax resolution for IF/AND/CONTAINS
Please help with syntax error. I'm trying to check a box if the following is true (in english): IF TEXT is not in Project Long Name column AND Over 90 has content OR IF TEXT is in the Project Long Name column AND Over 180 has content =IF([Over 90]@row <> “”, AND(NOT(CONTAINS("TEXT", [Project Long Name]@row))), OR([Over…
Status Bar Increases as 3 Check Boxes are Marked
Hello, I read through previous posts and am not landing on the correct solution for my status bar question. I would like supervisors to quickly see status bar updates on rows as team members mark checkboxes to indicate task completion. My columns are "Director Submission Complete", "Approved by Danielle" and "Submitted to…
Years of Service Formula Help
Hello Awesome Community, I have watched videos and searched the threads but I have hit a block and cannot figure out how to calculated the number of years my employees have worked. I have the Column setup as "Date of Hire" with the date in this format MM/DD/YY and I am trying to figure out the "Years of Service" to today's…
Help Article Resources
Trending in Formulas and Functions