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.

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Getting #Invalid Operation
I have the following formula and am getting an #Invalid Operation message. I only get the error though when it returns "Red". The column is defined as a Date and is restricted to dates only. I think it has to do with the TODAY() part of the function. =IF(AND([Due Date]@row < TODAY(), [Due Date]@row > " "), "Red", "Green")…
I am trying to return "100%" if the result of the formula is "0"
[column A] = Boxes on Hand [column B] = Target Boxes on Hand [column C] = Deficit of boxes on hand [column D] = Deficit percent of target boxes on hand If target boxes on hand [column B] = 6 and boxes on hand [column A] = 2, then deficit of boxes on hand [column C] =4. The deficit percent of target boxes on hand [column D]…
Column Formula - Populate data from 1 cell down each row.
Hi SMEs and friends, BLOT: I need Client Name (field value in [Project Task]2) and Client ID (field value in [Project Task]3) to populate for each row in [Client Name Helper] and [Client ID Helper] columns. This will be used for reporting and dash build out at a later time for a few hundred clients. I don't know much but I…
How to exclude data from Data Shuttle when updating dropdowns
I have data shuttle setup to update dropdowns in a sheet on a daily basis. It is a list of equipment housing in our ERP system. There are certain pieces of equipment, 5 in total, that we would like to exclude from the dropdown list because they are not owned by our company but we need them in the master file because we…
Expected % Complete not match automated % Complete Parent Rows Formula
I am attempting to do a Expected % complete and I cannot determine how to align my parent rows calculation to match the automated % complete calculation within Smartsheet. Currently all children row formulas are the following: =ROUND(IF([Estimated Start Date]@row < TODAY(), MIN((TODAY() - [Estimated Start Date]@row) /…
Using Contact List Column Type in formula
Hello, I am trying to find a way to setup a rule that will present a user a warning if they select a team member for a task from a Contact List Dropdown on a certain day of the week. From My research I have gathered that this is not a straightforward task. Is there a way to write the formula if referencing a helper sheet…
#NOMATCH Error
This discussion was created from comments split from: Does INDEX(MATCH) work on a column with a formula?.
Overall Project Health Formula
Hiya folks, We want one field to show Overall Project Health. Ideally, we would figure out how to average the colors and/or tasks past due to return RYBG for the overall projects (about ~230) so that we can report it on a Dashboard. Any ideas on formula would be so appreciated. Circular reference error formula in OPH row:…
Average Based on 2 Separate Criteria
I'm trying to create a formula that will average the age of an event if it meets two separate criteria: 1. The event occurred in that month (date of notification) 2. The event status is "Complete" The age of each event is already calculated for each event in a separate column. I'm getting a syntax error with the formula…
Index Match not working
Hello - I am trying to use a formula on sheet 1 to return a value from sheet 2 based on the matching of a task name. I have tried the below formula (in many adjusted forms) but cannot seem to get it to work correctly. Some troubleshooting I have done: Set up references to search the entire columns of "Task Name" and…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Popular Tags

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Question about copying a sheet
I use Smartsheet to track my yearly activities. I have a sheet that is connected to this sheet for calculations that feeds into a dashboard. Hopefully that makes sense. My question is, I want to copy this entire thing and start new for 2025, however when I copy, the calculation sheet is still pulling from the 2024 sheet.…
Help with revision of Nest IF Statement for Symbols
Need a revised formula, which is in the Certification Complete column. This worked great. However I was just made aware of something. Okay, @dojones provided a perfect solution. However, as we are all aware, we are not always told about all the pieces of the puzzle. that solution was: =IF(AND(ISBLANK(Certify@row),…
Sum of only last child in cell range
Hi there I would like to count the values of only the last children in the column. Some cells have one indent, some more, so I would like to know the formula, to count only the last indent children. Below is an example what I would like to achieve. The result of the formula should be 350. Additionaly I would like to…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags