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.

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Need help with formula to calculate end date with % complete.
Hello, I need assistance with creating a formula to look at the end date and % complete columns. If the end date is nearing and the % complete is less than 100% the corresponding RYG should appear. The durations of each task varies from 1 day to 100 days depending on the project phase. What formula do you recommend for the…
Return Multipe Cells
Hi Smartsheet community, I have the following columns: [Name] (text/number value) [Team] (drop down value) [Budget Milestone] (checkbox) [Program Milestone] (checkbox) [Project Milestone] (checkbox) Now I am unable to run a report because the what in report builder condition is either all 'OR' or all 'AND', not all tasks…
Counting using Countifs if a date is present
I'm trying to create a countifs formula where if it finds a date in a column and is a particular county, it'll count that line. I tried this and don't get an error, but it returns a zero which is not correct. Thank you!! =COUNTIFS({McAdvCareSiteReview Range 2}, (ISDATE(true)), {McAdvCareSiteReview Range 1}, "king")
Count multiple criteria in a row
I have spent hours scrolling through all the great advice in the Community, and there is not a formula that is working for me. I need to count all the Semi-Annual Update, In Progress, and Waiting on County in a column. =COUNTIF([Status Report]:[Status Report], "Semi-Annual Update") works great to count all of the…
max value by category
Hi I'm stuck on figuring this one out. I am trying to generate a sequential number("Sequence" column) by category ("Application"). I want the formula to calculate the max value of all the records in sequence where application = application in the new record (user would have selected this from a drop-down. this is what I am…
How to update a cell with a formula?
What would the syntax be in the formula portion of the update be? I'm confused because some of the formulas I work with have the double quote character in it. For example, I have this as apart of my request. "formula":"=IF([Manager]@row = "Tom Hank", "TM", IF([Manager]@row = "John Oliver", "JO", IF([Manager]@row = "Elvis…
Percentage of certain cells in a column
Probably it's easier than it looks to me, but I'm very new to Smartsheet and any help is highly appreciated. I have a column that contains name of several cities corresponding to complete projects in another column. I'm struggling to put together a formula that will calculate percentage of each city in the whole list. Any…
Trigger Report Due when month changes from modified date
Hi I have a basic text entry sheet for a dashboard report. Fields are: [Subject], [Details], [Modified], [Due] In the [Due] Column I want to show that the month has changed so I can trigger an update request. How would I go about doing this? Regards Steve
Count If less than 50 rows
Can you do A COUNTIF formula with less than 50 rows or does Smartsheet default to 50? If I delete the formula in the blank rows I get an "invalid reference" error message.
Formula Help
I am trying to create formula that subtracts to different dates in my data but instead of displaying the date of the result I want it to display "On Time" if less than or equal to zero and "Late" if greater than 0. I got the formula to display correctly but it is auto displaying in my columns where no data is entered.

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Popular Tags

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Index Match on same sheet only matches to the first key found at top of row
The Index Match on same sheet only matches to the first key found at top of row where I am expecting it to look at all rows in the sheet. Here's the formula in Col3. =INDEX([Capacity 1]@row, MATCH([Collapsing 1]@row, Key:Key, 0)) And the example is below: You can see that the ABC12345 under Collapsing 1 col matches to the…
Can an automation reference a column from a cell value?
I'm trying to track which users are granted access to various systems. What I would like to do is to have one grid which lists all the System IDs (and various properties as columns) with one system per row, and a unique System ID for each system. Then I would like a grid that lists each user as a row, and the column…
Formula to populate a cell based on another cell's formula criteria
Hi Again, I have a formula in row 1 of column Project Status that identifies the last value in the column that has any status except Not Started: =INDEX(COLLECT([Project Status]:[Project Status], [Project Status]:[Project Status], <>"Not Started"), COUNTIF([Project Status]:[Project Status], <>"Not Started")) In the example…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags