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.

Two dates between Now and +7 Days
Hi I am trying to return a count of dates between two dates that will move based on todays date, I have tried umpteen different approaches but cannot get the sheet to return a valid answer. =COUNTIF(MigrationDate1:MigrationDate3, >TODAY() <= TODAY(+7)) 19/08/20 19/08/20 05/08/20 The answer it returns is 3. I would have…
Have a different email UI than what's in the request an update form
Hello - I am sending out pretty lengthly update request logic emails. I was wondering if there was a way that when the person receives an email they see only a few columns, but when they click the link to update the form, all of the columns appear. For example - I'm sending out an update request with about 24 columns. So…
Does Smartsheet allow the calculation of IRR?
I have found a key element of Program Management that needs solution in the Smartsheet platform. The calculation for IRR is critical component and not available as general function. As you may know, this simple one varying net cash flow typical program. Does anyone calculating real-life program can share with me?.
INDEX & MATCH where the MATCH is based on a multi select dropdown?
Hi - I have been trying to work this out, read through a number of threads, but, cant see this question directly answered. Scenario: I have 1 sheet that has a list of tools and another sheet which is used to record who those tools have been handed out to by using a multi select dropsown column. What I would like to be able…
=Count(children()) of multiple parent rows.
I'm trying to count the contents of multiple children in different cells for a grand total. I've organized my information by the media outlet, which will count its children for an "at a glance" total for ease of use. I would like the parent cell ("2 open inquiries") to count all the children for a total of all open…
Auto Assign Contact Based on another column's content
Hello, I would like help with creating a formula to auto assign someone to a row based on who was originally selected. In this example, I want to manually assign an officer and have the Team Lead & Front Office Support to auto populate answers based on who was selected as an officer. Both Team Lead & Front Office Support…
How do I display a Max Date for Child Tasks only if there are no blank Children?
I have a sheet with many hierarchies of Parent and Child Tasks. I want to display the Max Date of the Children, but only when there are not any blank values for the Children. Objective 1 - Completion Date 8/10/20 • Task A - Completion Date 8/7/20 • Task B - Completion Date 8/10/20 Objective 2 - Completion Date TBD or blank…
Count completed dates once from two columns counting only the most recent competion date by week
I'm trying to count the number of quotes completed by week. The challenge is there are two departments who may work on a quote and each enter their own completion date. Sometimes only one department works on a quote so only one of the two completion date columns is filled in. In a nutshell I want to only count "Type A"…
Can Index Match pull multiple records?
I am trying to pull contract numbers from an Active Contract file over to a 2nd file using the customer name as the unique identifier which is working just fine via INDEX(MATCH), however, if a customer has more than 1 active contract it only pulls the first one over. What's the best way to pull all of the contract numbers…
Copy Rows to Build a Master Sheet out of Individual Sheets
I have ~40 project info sheets that are structured with columns to populate roll up reports for a portfolio-level executive 'dashboard'. These info sheets also have sheet summaries referencing the 'active status' and populating a dashboard with that content. I want to on my executive dashboard capture metrics (but can't do…

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

Get a Countif on values in a multi-select column of cells
I am trying to write summary formulas to determine how many values appear of each in the drop-down of a column. I tried adding a wildcard, I have tried HAS, and tried CONTAIN, but I'm still not getting the right values. Currently I am getting 0 as a return value on this formula: =COUNTIF([ROI Type]:[ROI Type], HAS([ROI…
summaries in report
Hi i'm trying to build a report with summaries options. i have two column that i want a Min and Max summary. the column for the Min i a texte column and works well. but the other column has formulas and the Max doesn't works. there is a way to fix that? (instead of creating another text column in the source sheets?) here…
Converting email addresses with hyphenated First and/or Last Names
I am trying to convert email addresses into a Name field with the employee's First and Last Name, capitalizing the first character of each part of the name and keeping the hyphen in any part of the name that has one. I started with hyphenated first names so taking shmoe_joe-bob@company.com and converting it into Joe-Bob…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags