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
Date & Time
Hello- How do I add the date and time from start of task to end of task from this form? I need to measure the date and time from start to finish. I'm using the FORM. Thanks.
Parent rolling up data in Children rows
Green line is the parent row everything inbetween is children rows Column 30 is counting rows from N/A column that are not ticked, which are the rows im looking for. so 6 rows are active rows which are being used. out of the 6 rows, 4 rows have been ticked in appoinment concluded column i want to roll up the count of 4 in…
COUNTIF FORMULA WITHIN DATE RANGE.
Hi All, I get invalid operation for the following formula. Any ideas on my error? Trying to count the number of times something appears in a column within a date range. Any help would be great please. Thanks. =COUNTIFS({TIME RECORDING}, ([Column7]@row), AND(@cell >= DATE( [Column6]2), @cell <= DATE([Column6]@row)))
Formula to Count Ages of Orders
I'm trying to create a formula that counts the open orders based on the date the order was created. The thing complicating the formula is that it's for a metrics dashboard, so I need it to count the orders that are between 1-3 months old, 3-6 months old, 6-9 months old, 9-12 months old and over a year. I'm at a complete…
Pulling data from one form to another
I am creating a sheet that will collect data from multiple regions through a specific contact. I currently do it through automation. When I fill in a specific region the email associated with this region is in the automation that I created and it populates the particular cell, and sends a form to that person. I am…
COUNTIF and HAS
I'm trying to use Sheet Summary fields to determine how many people have signed up to work a football game. The Sign Up columns are multi-select dropdown, and I feel like I've done this correctly, but it returns the answer of 0, despite my having 2 instances when they should be counted. Any thoughts to remedy?
CONTAINS or HAS formula extension
Hi community, I am looking to extend either a CONTAINS or HAS formula to pick up a dropdown value from one column and assign it a numerical value in a separate column. Can anyone assist in what is the most effective way to do so? The intention is when any value from the "Suburb Population" column is selected via dropdown…
Need help with conditional formatting
HI, I want to make a conditional formatting rule for IF "ETA" is greater then "TARGET RECEIVE BY DATE", then highlight yellow. The current columns are formatted for TEXT/NUMBER. Not sure if they need to be in DATE format within column properties.
Document Builder filename output
Good day I have a workflow that generates a PDF document and have mapped the document filename so the output filename is correct. The problem I have is that when our project manager makes an amendment to the document I need the version number to also show on the filename. Is there anyone that has had a similar problem that…
Automation Row Copy Conditions (where is checked)
Hello, I am using the Condition where column "Milestone" is "is checked" for a Copy rows automation. However, the checked rows and their children rows are all copying over. How do I prevent these extra rows from copying?
Help Article Resources
Trending in Formulas and Functions
Multiple Sequences Need to Be Complete/NA to Provide a Complete Value
Writing formulas is not a strong suite of mine - I've tried a few routes (IF OR AND) but I'm either running into invalid formulas or formulas that are not netting the right results. I am trying to write a formula where I'm looking for sequence values to be either Complete or N/A and based on those sequences being one of…
IF statement based on cells in same row
I have a sheet where every row requires two approvals before it's fully approved. What would the formula be to make this work? For example, approval column 1 is named "initial approval" and approval column 2 is named "COHS approval". Both cells are dropdowns with the options pending, approved, or denied. I want the first…
Need to add an or condition to Count/Collect Formula in Project Metrics Sheet.
I need to modify below formula to collect Project ID's on intake sheet where Project Status is "In Progress" or "Not Started". Below works only if Project Status is "In Progress" =COUNT(DISTINCT(COLLECT({Project ID}, {Project Status}, "In Progress", {Project Length}, Label@row)))
