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 calculation formula not working
Two date columns Last Service and Next Service I want "Next Service" date to be "Last Service" date plus one year. I created the following formula in the "Next Service" column: =IF([Last Service]@row <> "", DATE(YEAR([Last Service]1) + 1, MONTH([Last Service]1), DAY([Last Service]1)), "") It correctly calculates the Next…
Improving Formula Descriptions
I feel like it could be better shown, for a variety of examples, how formulas can be used. For example, this image doesn't really tell you what the answer to the example is. The example only shows that those data types can be used but doesn't help you understand what it does. Minor gripe I suppose. Also sometimes I wonder…
Updating a status for new form entries based on approval
Hi all. I need help please. I have a form that generates new entries on a list. They are set to default to a "Requested" status when they arrive on the sheet. Each entry is set to go through an approval workflow ending with a Senior Manager Approval. There are a number of columns but the pertinent ones would be: Status…
Checkbox Formula
I am struggling to use a formula within a checkbox column. I was able to complete the first function of finding the % complete of Children Checkboxes using this =VALUE(COUNTIF(CHILDREN(), 1) / COUNT(CHILDREN())) * 100 + "%" However, now I need to find the % complete of those total values. (So the totals of grandchildren…
Summarizing information with COUNTIFS and CONTAINS
I am trying to summarize information on my sheet with the following: COUNTIFS The CAPABILITY Contains "Development" State= Green Cross-Functional Team or Activity= INTEROPERABILITY Can someone help me with a formula?
Double Entry
Dear Smartsheet Community, Would it be possible to avoid double entry: For example: Column Order NR : can you get a notification error when a users adds an order nr which is already listed in that specific column? I know this is possible in excel Appreciate the feedback B. Regards Mitch
How to catch duplicate data using formulas or premium apps?
Hi, Good day! I need help in catching this duplicate data with the same Service Type and Last Name but the First Name is different because the first one has middle initial. Can this be caught by sort of using formula or premium apps like datamesh? Anyone have a great mind or idea how to make a new column and formulas then…
Help w/COUNTIFS, UNIQUE and specific year
Hi team - I have a sheet with a column called "Agreement Number" that I normally look down to get a unique count of agreements using: =COUNT(DISTINCT({(Source Data) Agreement number})) I now have data that I've placed in that sheet in 2020 and 2021. What I'd like to do is count the unique agreements in the "Agreement…
INDEX / MATCH "No Match" Error
Hello all, I'm attempting to create a status log, where each week individuals enter their updates in the row with that week's date. I want the parent row to then return a) the current week's date in the "date" column and b) the latest update in the "key accomplishments" and "key upcoming items" columns. I've created a…
Helper Column to compare Dates
Hi, I am working to figure out a way to compare actual project dates to planned project dates. I linked the actual project dates from our active program view. If the Actual End date does not pass the Planned End date I want the "State" Column to be green. If the Actual End date is past the Planned date but within 21 days,…
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…