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
Sign in and ask it here! The community's got your back.

📈 Wondering if there’s a problem?
Check out the Status Page and subscribe for notifications.

💡 Share your knowledge!
Help others by answering questions.

Formulas and Functions

Stumped by a formula? Ask for help or find examples to see how others use a formula to solve a similar problem.

Did I make it too difficult to calculate?
Hi, I am a rookie when it comes to formulas - and while I understand the basics, I feel stuck with one thing related to a sheet that will start coming data collection soon, and I want to ensure the formulas are all set before it starts. Here is where I am stumped: I have a column on my smartsheet where one option let's…
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…
Count & Sum Distinct Values based on Criteria | COUNT(DISTINCT(COLLECT(...) | Returning 1 ???
In a ROLLUP sheet, referencing a source sheet, I'm trying to count (and sum) DISTINCT values that meet multiple criteria. My formula keeps returning "1", when in the shown scenario below has "80" distinct values. Hoping I'm just missing something simple. Rollup sheet purpose = count the distinct values rented within…
Hopefully a pretty easy question that I need help with.....
Hi all, I am newish to using formulas, and I am stuck. All I really need to do is have one column return as "true" based on the status. I am getting an error message. This will allow me to calculate my data in an Excel pivot table. =IF(CONTAINS("COLUMN NAME",@row), 1, 0) What am I missing? Thank you!
Attempting to add similar values with Sumif
I have a sheet that could list the same values multiple times as they are being populated via a Zap from Google Sheets. So that I can find a true total value of the Job Name, I am counting the times that they appear on the sheet and then dividing the Total Amounts of Sale for a Job by the number of instances on the sheet,…
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 ;-)
Alert when 3 travellers on same flight number
I have a very simple form which records booking reference (text), flight number (text) and date (date) and Alert (free text) I want an alert if a third traveller is booked on the same flight and date.
Calculating how many days passed (between 2 dates)
Hi All, I am wondering if you can help. I have a very basic formula in place to calculate how many days passed from date 1 to date 2. I used NETDAYS formula. Since we use it to calculate the time from when the issue was raised until it was resolved - the end date is not always there straightaway which of course brings back…
countifs and a date that falls between two dates
Please help! I have two formulas that I am struggling with. I am trying to count how many tasks have been completed/closed within the past fortnight. I came up with the following formula but it gives me the 'invalid operation' error. What am I doing wrong? Formula=COUNTIFS({LV_ BLD CONST QUERY REGISTER _status}, "closed",…
Return the lowest cell that isn't blank based on MAX date.
I've got a sheet where we record chemical test results. Some of the tests are done weekly, some every few weeks. I've been using a MAX formula to return the latest result cells based on max date. However for the tests that aren't done every week, the MAX formula won't work as each new row often is blank. The 10.19 in the…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Trending in Formulas and Functions

If then formula help
Hi All! I have a column , Average Score, that has been calculated using other fields. I want to use the average score to determine if an initiative is a program or project. If the initiative’s average score is between: 5 and 9 it is considered a project and if the score is between 10 and 15, it is considered a program. I…
Dashboard Last Updated Date
I would like to display the last time the dashboard was updated. My approach was adding a date field in the summary data that automatically pulls the last saved date of the sheet. I don't think I can do a formula in the date field type. Any other suggestions?
Counting yes and yes w/notes
Hello, I am having trouble counting using the COUNTIFS formula to yes's and another category called "Yes w/notes". Here is the current formula that I have: • =COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is there a permit required?]@row, @cell = "Yes") / COUNTIFS([Are all workers wearing the proper PPE?]@row:[Is…

Formulas and Functions

Help Article Resources

Categories

Popular Tags

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags