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
Help with Category Set Up
Our company is having our department switch from airtable to smart sheet and I need help getting setup. Our department works on interior design/home staging so our records change frequently based on the status of the project (destaged or currently staged..and a couple of other staging types) On airtable when I change the…
Index Match Formula: no longer working
This morning our main sheet (that houses all of our pertinent data) would not load and would only display a message that said "An error has occurred in Smartsheet". After a while the sheet came back but now all of my index match formulas that referenced that main sheet are no longer working. Has anyone else come across…
Adding subsequent row ID numbers to prefix if cell is not blank
Hi all, I'm struggling to create a row ID field where it will populate an ID number ID_RC_1 and so on if the Employee Name cell is not blank. If it's blank then it will populate "-". Is there a way to create sequential numbering to be included after the "ID_RC" prefix? Looking for : ID_RC_1, ID_RC_2... is name cell is not…
Conditional Formatting
Hi, I have a formula in a customer field that identifies if a task is on schedule or behind, but I want to use a RYG circle that automatically updates based on the disposition of the formula. For example, if the task is shown as behind schedule, then I want the circle to be red. Is that possible?
Planned to Actual RYG
Seeking help on a formula to show RYG. I was getting an unparseable error, however most recently receiving errors regarding syntax. I want show the following: If % complete is 100%, Green If today is greater than finish date AND % complete < 1, Red If % complete is < 50%, Yellow else Green I have looked at this until I'm…
Metrics for Dashboard with Charts for Current User
Is there are way to identify if the contact listed in a cell is CURRENTUSER()? I would like to create a sheet with multiple metrics so that the charts displayed on the dashboard based on these so data for the logged in user. I understand that for reports filter can be used but unfortunately reports are not supported to be…
Check a box when a list of names containing duplicate names contains the first occurance of a name
I am looking for a checkbox formula or another callout that will look at a list of project names and return a flag or check or another identification of the first occurance of each project name in the list. I am trying to get an accurate count of the number of jobs in that list. The list is small at this point, only 2…
Summing a column if a date falls within a quarter.
Hello, Could someone help me with a formula that states the following: sum the square footage if Construction Start date is within the Quarter and the Status is one of these: Complete, Construction, Executed LOI, Priority. I'm thinking I may have to add 2 hidden date columns to clarify the different quarters. Formula page:…
Help! Multiselect Column Formula with INDEX, MATCH and HAS
Hello Smartsheet World! I am attempting to run a formula that will return contact emails depending on which departments are selected in a multi-select dropdown column. Sheet 1: Improvement Notes Smartsheet -Dept Attention column is a multi-select dropdown -Department Contacts column (in green) is a contact list that allows…
Workday formula
I have a workday formula in place now which calculates an expected due date from the date of submission. I've checked the results and find that the calculations are off by 10 days. The expected date should be a combination of two SLAs. The first calculating the due date from the submitted date using a department SLA of 2…
Help Article Resources
Trending in Formulas and Functions
COUNTIFS formula for any future date?
Hi all, I am trying to create a COUNTIFS formula that counts if the due date column on another sheet is today or any future date. I tried =COUNTIFS({due date column's range}, >=TODAY()), but it is not working. Any advice would be appreciated! Thank you.
Sheet Summary Field - Count Value from Dropdown
What's wrong with this Formula? Getting an Invalid Ref
I am trying to get a unit count between a date range.
I am trying to get a unit count between a date range. I have two difference columns from one sheet and two difference columns on my main sheet I am referencing. I keep getting unparseable. Is there a way to to get the number I want with using the countifs formula? Or should I be using a different formula? This is the…