Formulas and Functions

Archives for September 2023

Extract text from a file string
Hello! Simple question (I hope). I have a list of document locations - that look like this: _Central Documents/Training/Investigators/Rhodes, Meredith/CV I would like to parse 'Investigators' into a staff role column, 'Rhodes, Meredith' into a contact column, and 'CV' into a document type column. What is the basic formula…
Count Per Specified Criteria and Date Range
I am trying to create a formula to count the number of data quality issues per provider agency in a Smartsheet report. We would like to keep track of the number of data quality issues per provider agency each month. The data quality issues are entered through a Smartsheet form and displayed in a report. Each row signifies…
I am trying to convert a formula to a column formula and keep getting the same error. Why?
The formula I am trying to use is calculating the number of days between 2 dates: =NETDAYS([Billing Period Start]1, [Billing Period End]1) The error message I am getting is "The column formula syntax isn’t quite right, see our help article https://help.smartsheet.com/articles/2481944 for more information." The column type…
How to display a cell with a countifs formula as a percent?
I have one cell that has a COUNTIFS formula to count the total number of projects and another cell with a COUNTIFS formula to count the number of projects that will be complete by October 27th. I don't actually want a count of projects complete by 10/27 I need a percent. Can I divide the count in cell 2 / count in cell 1?…
Modify Dropdown cell value based on another cell
I have a column for positions (Chair, Vice-Chair, NA) from a dropdown list, I have another column for Removed which is a checkbox. I need a formula that will change the position value to NA if the Removed box is checked.
Does the automation to change cell only work for a checkbox and not for a flag? Very Odd
I am trying to make an automation where if the due date is in the past to automatically flag the cell under the flagged column. However, it will only work if that option is a checkbox and not a flag. I am confused why that would be the case. Is this a problem for others too?
Creating a chart from a multi-select dropdown among multiple sheets?
Hi there, I am running into a roadblock on creating a report and graph for the below scenario. We have a bunch of event sheets (about 10) that all have an 'assigned to' column that is a multi-select contact list and it varies between sometimes being assigned to just one person and sometimes to multiple. I am trying to…
Return Text that is in a Data/Number Column that captures dates
In the table below, I have 3 columns that are text/number fields: Final Document, Target Date, Actual Date. In the Target MM/YYYY and the Actual MM/YYYY, I have a formula to pull the MM/YYYY from the Target date and Actual Date, but as you can see, there is "On Hold" and "Cancelled" text instead of a date in some of the…
Auto Numbering
Good Morning, I have multiple sheets that filter into the master sheet. The individual sheets have auto number columns, which then transfer over to the master column. We would like to number each row individually of the master column, however the auto number option is already used. Is there a formula that can be used to…
Sum of rows in hierarchy
Hello everyone! How can I sum rows of duration (minutes or hours) in a hierarchy? I add an example: I need sum of total hours for every group. Thanks to who will help me ;-)

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

How to calculate NPS Score from Smartsheet Sheet
Hello, I have tried to calculate our NPS score, but am getting either UNPARSEABLE or 0. What am I doing wrong here? I've tried this formula (NPS Score 1): And this one (NPS Score 2):
Identifying oldest entry on sheet, for different groups
Hello! I am working on a "waitlist" process, where the sheet will have multiple candidates under each work location. I need a formula to identify the oldest entry for each location, and if there are multiple entries for the earliest date, use a "score" column identify the higher score as the next candidate in line. Example…
Week Over Week Change
I have a sheet that has orders entries from the last 6 months. This sheet continues to populate as more orders are made. This is just my repository sheet. I am looking to get the week over week change metrics for the last 7 days. Here's the formula that can provide me that week over week change: Week over week %% =…

Formulas and Functions

Discussion List

Help Article Resources