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.

Index/Match Function help for Indexing multiple Columns
I'm creating essentially a search engine for my organization to quickly pull data from a large data collection. I am running into a wall with the Index/Max function and trying to get Smartsheet to be searching multiple columns to pull the information from a column in the row with the #1 in the row. Staff will submit a form…
How do I trouble shoot wrong date/time in a created date column?
I have a sheet that records tenant inquiries. I do not have a date field in the form, I use 'created date' in the worksheet. It's been working fine, except for yesterday. I received two tenant inquiries with days in November and weird times. I'm not even sure how to trouble shoot this. I went into my date and time settings…
Formula help please! What formula to mark the parent row "Complete" with an "Not Applicable" task?
In my status column I am using this formula auto update my my parent row: =IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", IF(COUNTIF(CHILDREN(), "Not Started") = COUNT(CHILDREN()), "Not Started", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Not Started") > 0, "In…
Need formula to remove characters from a column when added
We export data from another data source and cut and paste it in the Smartsheets Column AOY Status. The issue is after we cut and paste the data there are going to be certain characters I need removed. For example: The data we cut and paste in will have Down <20 days or Down >20 days. When we cut and paste that data I would…
Formula to calculate the difference between two numbers based on a month in another column
I am unsure if it is possible to do what I want, but I wanted to ask. I have attached a copy of my grid. I want a formula to calculate the difference between the largest and smallest numbers for each well for each month. For example, for well #2 in month one, find the largest number (58650300) and subtract the smallest…
Get the DisplayValue of a Hyperlink
I have a column of currency values that are hyperlinks to receipts. I want to total the column but the SUM function does not recognize the displayvalue (the currency amount). How do I Get the DisplayValue of a Hyperlink? Thank you in advance
Triggering an automation from an attachment revision
Hi - I am trying to trigger an automation when I upload a revision to an attachment. I already have the document generated by changing a field and then when I upload the revision, I want the same field to change again. For some reason, I can't seem to trigger from the revision. I tried setting the trigger to a new…
Subtraction formula with conditional IF statement from cells populated from index match
Hi everyone, I'm trying to create an inventory spreadsheet that auto calculates the QTY in the warehouse by subtracting the amount received from the amount signed out. These columns are populated via an index match formula cross referencing another sheet. I also need there to be the caveat that if nothing has been signed…
Automation for creating a Template of new child rows when a Parent is copied from another sheet?
I'm working on creating some workflows that will copy information from a change order tracker sheet into a resource assignment tracker sheet. Ideally, I'ld like to grab key details from the change order tracker (number & name) over to a resource assignment tracker, then have additoinal rows added on the resource assignment…
Sheet Title Formula?
Does anyone know if there's automation or a Formula where I can pull the Sheet Title into? For example, I want to create a "Project Name" column in a template that will bring the Sheet Title down to the row level. The use case is to then have a dashboard/report that will combine all my project sheet tasks into one. I know…

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

I have the Year and Month on a sheet. How can I have the summary look at both to count?
I would like to create a summary if the month is Sept AND the year is 2024 to count. I have found that I can use the and but keep getting an error message This is the Formula I have in the Summary that is coming up #Unparseable =COUNTIFS([Next Renewal Year]:[Next Renewal Year], "2024") AND(([Next Renewal Month]], "Sept")
Formula Help
Hi All - I am hoping someone might be able to help me. I am terrible at formulas and I cant seem to figure out how to pull what I need on my own so I copied a formula from another sheet we have at our company. The issue is that it returns it multiple times. So the expected result is "four", the formula returns "four"…
Are You Able to Join Two Columns into a Data Shuttle Dynamic Dropdown?
Hi everyone, I'm hoping someone here can help me out with a challenge I'm facing in Smartsheet's Data Shuttle. I've been trying to use expressions to combine two columns into a single dynamic dropdown column in a target sheet, without changing the source sheet. For example, I want to combine Num and Name into a format like…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags