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
Sign in and ask it here! The community's got your back.

📈 Wondering if there’s a problem?
Check out the Status Page and subscribe for notifications.

💡 Share your knowledge!
Help others by answering questions.

Formulas and Functions

Stumped by a formula? Ask for help or find examples to see how others use a formula to solve a similar problem.

Is there a formula/function to help count number of children assigned to specific parent tasks?
Hi all, I'm trying to put together a table that lists the number of sub-tasks (children) in each respective Workstream (parent) of a specific Project Plan, and also breaks these down into their respective Health Status (Green, Yellow, Red). I am aware that I can apply a "COUNTIF" formula and set the range to encompass the…
=average of more than one criteria
=AVG(COLLECT([Closing Date]:[Closing Date], "", [Days ago]:[Days ago], (Category:Category, "Purchasing"))) Good Morning I'm trying to average the closing date of a row but i also want to itemize the closing dates by category. no sure what I'm doing wrong, new user
Numbers separate by commas read as thousands
I have ONE cell with various numbers/text that users get to input via a form. The input varies from all numbers separated by commas or can have some text as well. If it is just numbers separated by commas and there are no spaces in between those numbers and commas then cell is read as number. I need some sort of a way /…
Time Tracking - Hours and Minutes
I was working on a way to track some task hours and minutes... came up with this: One of the problems I ran into is that if you put "00" (double-ought) or any minute with a preceding zero, it would error. You can see the work-around I made, not the most elegant, I admit.. If Smartsheet could ignore preceding zeros, it…
Two criteria to enter text into a cell - automation not working - use formula instead
Hi! I need to have text entered into a cell based on two criteria: a cell being completed and another cell being a date in the past. I have tried automation and it's not working. Maybe because I have formulas in the cells I'm referencing? This formula is not working: =IF(AND([TCP Deadline]1 < TODAY(), ISBLANK[Date…
Is there a way to have a column formula automatically change for each month of the year?
I am tracking financials and we have two important numbers coming out of a row across 12 columns labeled by month. The first number is the "forecast at completion". Its a simple sum of all 12 columns, regardless of it being past of future month. The second number is the "forecast to completion" which is the sum of only the…
Automation not working as intended
Hi all, I have a sheet in which I'm tracking installation dates of equipment (outside vendor) and have automation to fire off to ensure the necessary pieces all fall in line (in house), within a timely manner. However, something must have changed in the last couple of weeks, because it's fallen on its face and I'm having…
Run formula on creation only
Hello, I am trying to have a formula in my primary column to have a date-number of tickets created on this day. so I would have 11/02/21-1 in row 1, 11/02/21-2 in row 2 and so on. However, when A cell is added above it it the formula updates so 11/02/21-1 becomes 11/02/21-2. IS there a way to lock the formula to only run…
Conditional Formatting between 2 different Sheets
Hello, I am looking for an option to have a conditioning rule in one sheet and once changes, it triggers a format change in another sheet? I saw this question came up in 2016 and the support team said they will pass on as an enhancement request. Can be done now? Thanks!
How to convert a formula on a column formula by doing absolute reference
Hi, I would like to do some calculation on the same sheet that I am recolting data, I have a recolting data culumns : Culumn 2 and i would like to multiply the Culumn 2 with a fix value on Column 3 I am using a simple formula Culumn 4 =([Colonne2]@row * [Colonne3]@row) , how to fix the culumn3 value and convert it on a…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

If then formula help
Hi All! I have a column , Average Score, that has been calculated using other fields. I want to use the average score to determine if an initiative is a program or project. If the initiative’s average score is between: 5 and 9 it is considered a project and if the score is between 10 and 15, it is considered a program. I…
Dashboard Last Updated Date
I would like to display the last time the dashboard was updated. My approach was adding a date field in the summary data that automatically pulls the last saved date of the sheet. I don't think I can do a formula in the date field type. Any other suggestions?
Counting yes and yes w/notes
Hello, I am having trouble counting using the COUNTIFS formula to yes's and another category called "Yes w/notes". Here is the current formula that I have: • =COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is there a permit required?]@row, @cell = "Yes") / COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is…

Formulas and Functions

Help Article Resources

Categories

Popular Tags

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags