Formulas and Functions

Archives for September 2022

AND function
Hi I am trying to write a formula that would count the number of calls in the last 15 days. Below is my formula. I am first looking at my date column so that it is in between today and 15 days ago. Then I need to only count for if the Type of Dealer (Single Select Drop Down) = DAP Dealer and Merchant if they are New and…
How do I have the risk flag turn red for any line with status not completed by the end date?
I only have a completed date in there instead of a start and end date, is that why? I can't figure it out.
Copy Row Automation
Hello! I have been tasked by my team's leadership to create a way that they can audit how often my colleagues are updating their project plans. We currently have a "Master Sheet" that lists all of our current projects and certain cells linked in from each project plan. The project manager is supposed to be updating their…
how to calculate the number of projects who have an end date within 14 days?
I want to know how to calculate the number of projects who have an end date within 14 days but are in a specific portfolio status?
Average formula with error should give a blank cell
I used this formula below to give me the "DOC % Complete" but it is missing something. If the cell has “Cancelled” the Docs % Complete column should be blank but right now is giving me 50% =IFERROR(AVG(ISDATE([Design Circuit Recommendation Actual]@row), 0.5) + IF(ISDATE([Design Velo Recommendation Actual]@row), 0.5), "")…
Formula using the RYG status balls
I'm trying to write a formula using the RYG status balls. These are the criteria: Green= If the "Deployment Complete" box is checked Yellow= if there is a date populated in "Module Go-Live Date" Blue= for anything else (deployment is not complete and Go-Live is not scheduled) I only got this far with the formula and (of…
Need help with nested formula to convert words to numbers in a row and then tally the result
I've created a sheet that's effectively a scorecard. Each column is a different area being measured. In one of the columns, I need to assign a value each time a particular word appears and tally the total score. It seems that what I'm missing is a reference to all of the columns in the section that needs to be tallied but…
Use IF And Statement to Flag
This formula works correctly: =IF(AND([Date Created]2 < TODAY(-3), NOT(Status2 = "Completed")), 1, 0) If a ticket was created more than 3 days ago and the status of the ticket is not completed, flag the cell. It works. Now I need to add Duplicate in addition to Completed. No matter how I change it I always get an error.…
Count ifs for children with multiple criteria
Hi! I am trying to count children that DO NOT have a date in one row but DO have a date in another. I want to know how many active employees have completed training. (don't count inactive date but do count complete date). =COUNTIFS(CHILDREN([Inactive Date]@row), ISBLANK(@cell)), CHILDREN(Date@row), ISDATE((@CELL) This is…
Need to check a box if any of these are true
Hi, I'm trying to check a box if Sales Status equals either "Deployment Prep Call - Scheduling", "Deployment Prep Call - Scheduled" or "Verbal Agreement" The formula works for one of the three choices: =IF([Sales Status]@row = "Verbal Agreement", 1) However, I'm getting an INVALID DATA TYPE error when I have all three…

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 Can I Calculate the Number of Hours between Two Different Time Formula Values?
I have a form that collects times of day, into a sheet via a dropdown List of times (9:00 AM, 9:30 AM, 10:00 AM, ect). I have converted these values into a time using the TIME function. So now I have 4 columns: Start Time (dropdown list value from the form) End Time (dropdown list value from the form) Start Time 12 Hr…
Automatic Phase from WorkBreakdown
Hello all! I'm modifying the Simple Portfolio Work Breakdown Schedule and having a little trouble. I like the way the Work Breakdown structure works but I'd really like an automatic way to track which Phase something is in. I know I could just add a column and manually select which Phase tasks and sub tasks are in, but I'm…
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):

Formulas and Functions

Discussion List

Help Article Resources