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
Status with Predecessor
Hey All, Need help with a formula. What I have works, but I want to add another condition to it though I am not sure how. I do not want the status to show as ready unless the predecessor is complete. I'd like a copy/paste formula vs referencing to a specific row. Thoughts? Here is the current formula =IF(AND([%…
Count if formula help
Hi, looking to get total of items flagged "1-CRITICAL" under "Priority" column, that are not marked "COMPLETED" in "Status" column. No matter what I do - I get an error.
I want to create a very basic stock management sheet. adding in stock updates quantity and
removing stock updates the quantity. I cannot figure out what the easiest method would be without creating a loop because if I add stock I would have to keep upping the numbers of that column. If I already had a quantity of 20 water bottles and then I got in another 20 bottles of water one week, and the next week I got…
The IF function
I want to be able to type in the field if the Value is False. I can copy the formula to all the field in the column and overtype the formula. but if I convert to a column formula then I cant do this. Any ideas?
Trying to reference two sheets but having trouble with second reference
Hello, I created active project and archive project sheets. When a project is completed, it is automatically moved to the archive sheet. I set up a timesheet that references both sheets and looks for the project ID to automatically fill in the three fields for project name, project type, and solution type. It works fine…
How to auto compute the task Status based on the task and predecessors % Completion status
The snapshot is my sample Project Plan. From this data table, appreciate if the expert can help me to derive the Status column formula based on the following remark: 1. Status column with the status Not Started is the % Complete equal to 0%. 2. Status column with Complete is when % Complete archive 100%. 3. Status column…
Removing Conditional Data
Hi, our company wants to select two best employees using Smartsheet, now we have three requirements, 1. For repeated [Creat by], only the latest data will be kept; 2. If [Selection 1]=[Selection2] the ballot is also invalidated; 3. Count the number of valid selections. Can you please help me to create this formula? Thanks…
Convert date format from US to EU
I need to convert a date from a USA format (MM/DD/YYYY) to a EU (DD/MM/YYYY) without changing the region in my settings.
DATE Formula: How to calculate a half-day?
I have a Paid Time Off (PTO) tracker, whereby the company allots a full day's pay for major U.S. holidays - and a half day's holiday pay on New Year's Eve (Dec. 31st). Below is the applicable part of the formula: Employees submit a form requesting how many days they want off, what the start date is and what the last date…
Data updated bidirectionally???
Please Help! We utilize an intake form/sheet that, upon meeting specific criteria, is automatically copied onto one of three designated "Master" sheets. All subsequent actions and tasks are performed within these Master sheets, Is it possible to then have the Mastersheet updates are reflected back to the original intake…
Help Article Resources
Trending in Formulas and Functions
Why is 'DAY' Value from a Date being populated incorrectly?
I have a 'Formatted Date' column with a column formula (below) that formats the date from the row's date timestamp, which I renamed to 'Training Completion Date', into DD-MMM-YYYY format. All rows but one is getting the correct day from the DATE. I cannot figure out why 10/08/24 returns 9-Oct-2024. Curious to know if…
Counting items in a cell separated by comma
Hello, Thank you in advance. Need help getting the count of the values in a cell that are separated by a comma. Example in a Cell I could have three values: Blue, Green, Yellow. Need to return the value 3 but I can't seem to find that anywhere in the community. Thank you again!
Need a formula for % complete for all phases on the parent project summary row. Row is non editable
I need an average formula in the parent project summary (row 23). Formula should include all phases (initiating, planning, executing, closing). The parent row is un-editable and not allowing me to enter a formula.