Formulas and Functions

Let the community help you with that tricky formula or function! See how others are utilizing the power of formulas and functions to save them time and create more powerful solutions with Smartsheet.

Discussion List

  • Formula help - due dates falling on weekends
    In Formulas and Functions
    I've got formulas in my sheet that calculate due dates that fall on weekends, and I've used conditional formatting to highlight those dates to alert me to those dates that are on Saturdays and Sundays.  Now, I want to move those dates to t…
    51 3
    51 views 3 comments Most recent by Paul Newcome
  • Sorting Numbers with a "-" in it
    In Formulas and Functions
    Hi all, Probably my incompetence here but i have a confusing issue at the minute. I have a long list of job numbers and am trying to sort them in numerical order.  There is a criteria that we use to create these that you should probably …
    51 6
    51 views 6 comments Most recent by Mike Wilday
  • Aging Date Average
    In Formulas and Functions
    Hi Community, I am attempting to create a KPI for a dashboard that will capture the average number of days a 'something' has been opened, but only if it is still in an opened state.  I can perform the appropriate "days open" wit…
    73 2
    73 views 2 comments Most recent by Beemer Bum
  • Linking/adding rows using 2 spreadsheets
    In Formulas and Functions
    Hi all, I am having an issue that might be simple to solve, but I can't seem to find an efficient solution. I have 2 spreadsheets: - One with the ID, name, Lot and several other columns with product specification information - Another …
    1 2
    1 view 2 comments Most recent by Paul.Koetke
  • Countif children row with reference another sheet
    In Formulas and Functions
    I want to count the children rows in another sheet that contain a certain value, my attempt gets an #UNPARSEABLE   =COUNTIF(CHILDREN(){Lease Tracker 2018 Manager},="CORNELIUS"))   Any thoughts? THANKS!
    348 4
    348 views 4 comments Most recent by eric.o
  • Capturing Date of Transaction
    In Formulas and Functions
    Greetings, I would like to capture and freeze the date a drop-down column gets changed to "Picked Up by Customer".  I tried modified date but that field gets changed too often to accurately reflect the state.  Essentially, I wan…
    11 2
    11 views 2 comments Most recent by Andrée Starå
  • VLOOKUP - I think?
    In Formulas and Functions
    I am doing estimates for projects.  Each job title has its own rate and there are different rates based on specific clients.  What would my formula be if I want to reference a particular cell based on these two parameters.  They would live…
    1 1
    1 view 1 comment Most recent by Paul.Koetke
  • Overall Average Percent Compete
    In Formulas and Functions
    Hi All,   I have looked around on multiple forms and haven't been able to find a solution for why my formula isn't working.I am trying to determine the formula for the total project % complete of the children rows. For the Project Initi…
    965 12
    965 views 12 comments Most recent by Andrée Starå
  • How to Create Tend Arrows with a Function
    In Formulas and Functions
    I have a cross sheet table with data displayed on it that works fine. Just wondering how I can display a trend arrow using a function for Up, Down and Across.   Basically, I am measuring KPI performance against the number of days since t…
    368 5
    368 views 5 comments Most recent by Andrée Starå
  • IF function with many conditions
    In Formulas and Functions
    I have an internal client who wants to parse a list of 100 different zip codes, and depending on the zip, assign another text field into a different field. In other words, IF zip code = 11733 OR 11725 OR 11890 (and so on) THEN assign to N…
    11 3
    11 views 3 comments Most recent by Paul Newcome