Formulas

 

Discussion List

  • Formula that can be applied as a Column Formula that marks unique values/entries out in a list

    Hi All - I have a sheet that has a list of URLs in one column - lets call that column [HIRE URL] Some of these URLs are duplicated and I am looking to use a Helper column to mark which ones are unique entries. I found this formula in another forum post which I adapted to my needs; =IF(COUNTIFS([HIRE URL]$1:[HIRE URL]@row,…

  • Pulling a Rating from a Matrix

    I have this data in a Smartsheet file (exported to Excel below) and I need to pull a Risk Rating from any of the nine combinations of two variables. Thanks in advance for the help!

  • I want to count the number of times values appear in a multiselect column

    I am not sure how I can combine INDEX MATCH, HAS, and COUNTM functions for me to be able to count the number of times different values appear in multiselect fields

  • Input the date for a multi-select dropdown

    Hi all-- I am trying to create an efficient way to track my samples as they move around to different facilities. Please see my screenshot for what I am working on. Ideally, I'd like to just have the check box as in the screenshot because it's easier and faster. Space on my SmartSheet is also a premium. Now, I'm aware that…

  • task status based on expected % complete

    I am trying to write a formula that will return red, yellow, green, or gray staus symbols based on the following criteria: Gray - If actual % complete = expected % complete Green - if actual % complete > expected % complete Yellow - if the actual % complete is within 10% below the expected % complete Red - if the actual %…

  • CONTAINS with Trigger Search

    Hi all, Thank you in advance for the help. Below is what I am trying to achieve. The columns are as follows: Address and Device Type Explanation: When Device Type contains the word "Coin". This will use the address of the device type that contains "Coin". This address will then be checked against the address column until…

  • Nested IF, OR, AND with TODAY formula to return a symbol (RYGB) based on "Drop Down Menu Status"

    Hi All, I'm trying to set a symbol (Red, Yellow, Green, Blue) in a column based on the individual values in one column. Here is what I have so far (but it is wrong): =IF([Current Status]@row = "Sarepta", "Green", IF(OR([Current Status]@row = "Thermo", [Current Status]@row = "Catalent"), "Yellow", IF(AND(TODAY()[Current…

  • Adjusting for 'On Hold' time in a Turnaround Time Formula

    A scenario was brought to me today about a concern with our user's turnaround time metrics. What I'm using today is =IF([Status Summary]@row = "Closed", NETWORKDAYS([Assigned Date]@row, [Status Date]@row) - 1, "TBD"). This works great if the requests can be worked on in a timely manner. However, the issue at hand is that a…

  • Need help with a SUMIF formula to sum the number of credits with multiple AND and OR functions

    This is a continuation of the question that was answered here: https://community.smartsheet.com/discussion/91230/need-help-calculating-number-of-credits-with-multiple-and-and-or-functions#latest The answerer found the correct formula! Results are in the screenshot below. I need help creating the correct formula syntax in a…

  • Countifs with Today Function

    Hi! My team and I are having a hard time with some of our formulas. We need a count for how many projects that are ending in 30 days and as well as 90 days. Our currently formulas are not providing accurate numbers based off our what our filters are showing. Complete next 30 days formula: =COUNTIFS({HR Projects - Complete…