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
How to calculate due dates based on 2 criteria fields
I want to have the due date column auto populate based on project start date and # of days for each category. Is this possible? So all tasks in project level rows would be due 30 days from start date, all EVAC tasks would be due 90 days from project start date.
Metrics - how to question
I want to change up how I am calculating metrics - currently counting tasks by team / completed tasks but this looks like not much progress is being made. Instead we would like to do this more by points or % of larger categories. For example: EVAC tasks would total 50% of the overall project, AWARE/community tasks, 40% and…
Formula preventing manual entry to cell. Is there a possible workaround?
Maybe a little long, but some backstory is necessary to get a better understanding on what's going on and if I'm approaching this issue right. Currently my Smartsheet is setup where there is a formula in the "Status" column that checks if there are less then 2 names in the "Total Emails per Application" Column. The "Total…
Copy a date from one cell to another when a status changes
I am looking for a formula to to copy a date from one cell to another when the status changes to "In Progress". The formula below works but every time the date in cell 1 changes it changes in cell 2 because the status is "In Progress". I only want the date to copy to cell 2 when the status changes to "In Progress" the…
Different between Excel and Smartsheet Formuale
Hi Team, Would you be aware of to why Smartsheet would calculate the same data differently. I ran the Pivot for validation and those numbers don't tie back to how Smartsheet picked it. The COUNTIF formulae and all is correct with no errors or anything. Even with status types GH IJ and KL where one can filter out data…
I want a formula that counts (Request Type by Month).
I have a metrics sheet and the RE Execution Request sheet and have attempted many versions of this formula for the highlighted cell: =COUNTIFS({HEOR Research Execution Request Form Range 4}, AND(IFERROR(MONTH,0) = 8, IFERROR(YEAR,0) = 2021, {HEOR Research Execution Request Form Range 1}@cell = "Prospective Research…
IF Formula comparing SUM values
I am struggling too figure out how I can make this formula work: =IF((SUM([Billable PM Hours]@row, [Billable Consultant/Developer Hours]@row)) > (SUM([Consultant/Developer SOW Hours]@row, [SOW PM Hours]@row, 1) Intent is to have these Sums compare to each other so that when Billable Hours Sum is over the SOW Hours Sum, the…
Conditional Format
I have built a conditional format into my form sheet. Is there any way that I can get the color of the conditional format to appear on the PDF mapping document after the form is filled out and ready to print?
Query
I created a query and it works great the only problem is I would like to hind the query behind a check box selection. Currently the query is next to the check box column. Is there a way to check the box on my sheet and the form open automatically and auto filling the cells that I created in the query?
Returning just one record per name (Talent) showing max total points (Summed Total by Talent)
Hello - trying to determine if this is even possible with my smart sheet. I have multiple rows with the same talent (name) and same max total of points (Summed Total by Talent) as a new row/record is added whenever a form is submitted. Here is a screen shot: Will i need to add a new column/formula to just return one record…
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…