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

Parent Color Symbol, Based on "Average" of Child Symbols
Okay, here I am again! On the Project I am working on, the Stakeholder wants parent rows for no reason other than to be able to expand and collapse the data. I nicely suggested that since the data would only be for 1 year (broken down in quarters and about 5 users listed each quarter), that since the data was not excessive…
Can someone please help me with a COUNTIFS formula?
I can't seem to get this formula to calculate any results other than 0 COUNTIFS({11.08.2024- Labor Allocations for Smartshe ES}, [Expense Specialist]@row, {11.08.2024- Labor Allocations for Smartshe FU}, OR(@cell = "410101", @cell = "41201", @cell = "410202", @cell = "410203". This is only a part of a larger formula.
Determine follow-up date that will trigger Workflow
I want a date to populate a field that will trigger a follow-up workflow. My criteria is: If the certification Complete is GREEN, this date can remain blank, as no follow-up will be sent. If the certification Complete is YELLOW or RED, the Manager has either partially completed (yellow) or not completed at all (red). The…

Formulas and Functions

Discussion List

Help Article Resources

Categories

Popular Tags