Formulas and Functions

Welcome to the New Smartsheet Online Community


You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.

Formulas and Functions

Discussion List

  • john.spikula42751
    john.spikula42751 21 views 3 comments Most recent by Mike Wilday
    First post 03/28/18,Last post 04/16/18
    Looking for help... I'd like to update the fields on a form dynamically. Example:  Use the system-generated Creation Date to calculate a "Delivery Date" 2 weeks in the future and display it on the form.  I know I can do this on…
    Formulas and Functions213
    john.spikula42751
  • ggross
    ggross 5 views 1 comment Most recent by Mike Wilday
    First post 04/16/18,Last post 04/16/18
    Is there a formula that would count the number of cells where a Done check is not completed but only when another columns due date is in the past?   The goal is to count how many past due tasks I have.  Does anyone know the best way to a…
    Formulas and Functions51
    ggross
  • robby.martin44926
    robby.martin44926 11 views 1 comment Most recent by Mike Wilday
    First post 04/16/18,Last post 04/16/18
    I am trying to use SUMIF to add the dollar amount for jobs that have 100% probability.  I wrote this formula: =SUMIF(Probability:Probability, "100%", [Quoted Amount]:[Quoted Amount]) and it returns a 0 but no error.  Also all of …
    Formulas and Functions111
    robby.martin44926
  • Grace
    Grace 111 views 6 comments Most recent by Mike Wilday
    First post 04/02/18,Last post 04/16/18
    Hi all, I'm pretty new with formulas and I wanted to automate the status of each projects based on dates. I have columns Status and Progress. The status column have a selection of: Completed, Active and Not Started and the progress column…
    Formulas and Functions1116
    Grace
  • Kam
    Kam 6 views 5 comments Most recent by Kam
    First post 04/15/18,Last post 04/16/18
    Hi All: I only want this formula to activate only if it is greater than or equally to today's date, but having trouble adding TODAY part of the formula: =IF([% Complete (Actual)]3 > [% Complete (Planned)]3, "Up", IF([% Compl…
    Formulas and Functions65
    Kam
  • tylermh
    tylermh 14 views 7 comments Most recent by Mike Wilday
    First post 04/06/18,Last post 04/16/18
    As one of the pieces of logic in a formula I'm attempting, I want a count within a range (column) if the date (formatted as MM/DD/YY) falls within the year 2017.  What do I need to change in the formula as shown in my screen shot? (Image)
    Formulas and Functions147
    tylermh
  • Stevie Goodwin
    Stevie Goodwin 29 views 3 comments Most recent by Jim Hook
    First post 04/11/18,Last post 04/16/18
    Hi,   I am having problem with a VLOOKUP process.   Our current process flow is as follows   Import data into a "DataDump" sheet through API, at this point each row of data is assigned a number, matching that of the row num…
    Formulas and Functions293
    Stevie Goodwin
  • jerry.fokas44586
    First post 04/12/18,Last post 04/13/18
    Any update on the functionality for the "Reminders" in "Alerts and Actions" to be conditional to eliminate for example tasks based on a completion condition?  I used the work around proposed previously in this forum (se…
    Formulas and Functions111
    jerry.fokas44586
  • viknutson
    viknutson 27 views 6 comments Most recent by [email protected]
    First post 04/12/18,Last post 04/13/18
    I am in process of transferring a large excel document into Smartsheet (over 2000 lines).  The excel version is tracking 2007 projects, some of which are complete, some no longer active, and some active.  I need to count how many active pr…
    Formulas and Functions276
    viknutson
  • MRCNathan
    MRCNathan 9 views 3 comments Most recent by Chris McKay
    First post 04/12/18,Last post 04/13/18
    Hello   I have a quote log that has a win/loss column. I want to populate a cell that takes the $ amount of the won bids, adds them up to show a total $ amount of won bids. Same for lost bids.  I also want this result to auto update if …
    Formulas and Functions93
    MRCNathan
  • Randy Van Winkle
    Randy Van Winkle 13 views 1 comment Most recent by Chris McKay
    First post 04/11/18,Last post 04/13/18
    Hello, I am trying to build a formula that will do the following:   * Yes/no drop down column = Yes * Verification check mark column = checked * Health symbol column turns green If either column is blank the health stays blank.
    Formulas and Functions131
    Randy Van Winkle
  • aqeelsidd
    aqeelsidd 9 views 3 comments Most recent by Chris McKay
    First post 04/12/18,Last post 04/13/18
    Hi,  I am trying to combine two formulas into one, both, are correct on their own but I am unsure on how to combine them. Formula1: =IF([% Complete]7 < ABS(0.6), "High", "Low") Formula2: =IF(Blocked7 = 1, "Hi…
    Formulas and Functions93
    aqeelsidd
  • Andrew V
    Andrew V 30 views 1 comment Most recent by Chris McKay
    First post 04/12/18,Last post 04/13/18
    Hello, I have created a vlookup that works but cannot seem to create a second one.  Working: =VLOOKUP([Currency Code]55, {Customer FY18 FX Rates Range 2}, 4, false) Not working: =VLOOKUP(Country55, {Region List Range 2}, 4, false) I ca…
    Formulas and Functions301
    Andrew V
  • Sally Osborne
    First post 04/11/18,Last post 04/12/18
    I am trying to achieve this - If the action required by and the data processed is blank or the action required by = "DAT", then "DAT" is the correct response.   This what I have come up with  =IF(OR(AND(ISBLANK([Action…
    Formulas and Functions221
    Sally Osborne
  • Laura
    Laura 7 views 4 comments Most recent by Matt Hagler
    First post 04/12/18,Last post 04/12/18
    See Screenshot Example Please Goal: to compare cells in 2 rows across columns. All numbers in both these rows are based off formulas.  I want the cell in row "needed machine hrs" (column 1) to turn RED, IF the number is great…
    Formulas and Functions74
    Laura
  • JJansen71
    JJansen71 13 views 2 comments Most recent by JJansen71
    First post 04/04/18,Last post 04/12/18
    Presently I have a RYG IF formula to do the following. =IF([Commitment Expiration Date]11 < TODAY(), "Red", IF([Commitment Expiration Date]11 = TODAY(), "Yellow", IF([Commitment Expiration Date]11 > TODAY(), &quo…
    Formulas and Functions132
    JJansen71
  • brandon.elmer
    brandon.elmer 25 views 3 comments Most recent by brandon.elmer
    First post 03/13/18,Last post 04/11/18
    Is there a way to write a COUNTIF formula to count the rows that do not have any children?  I would like to write a COUNTIFS formula that counts: 1. % Complete is < 1 2. Finish is in the past 3. Row doesn't have any children I know …
    Formulas and Functions253
    brandon.elmer
  • lpollastrini44421
    lpollastrini44421 21 views 1 comment Most recent by Jim Hook
    First post 04/11/18,Last post 04/11/18
    Hello all, I am relatively new to Smartsheet and have hit a wall in trying to automate a RYG. I am looking to change the color by listing multiple possibilities from a range of cells. My idea was to create a separate column for the formul…
    Formulas and Functions211
    lpollastrini44421
  • Jayson
    Jayson 23 views 2 comments Most recent by Jayson
    First post 03/13/18,Last post 04/11/18
    Hello,   I have created a web based form in which the user fills in certain information. As part of our metrics we need to keep track of when an quote is finished and completed. I have a date column for the quote finish and a checkbox ce…
    Formulas and Functions232
    Jayson
  • Grace
    Grace 10 views 3 comments Most recent by Grace
    First post 04/02/18,Last post 04/11/18
    Hi again, I am new on formulas and been conducting some tests on my project tracker. I wanted to get the monthly savings on projects with completed status. I always get a zero even if there's a value on my savings column. Please let me k…
    Formulas and Functions103
    Grace
  • Laura Senff
    Laura Senff 17 views 1 comment Most recent by Shaine Greenwood
    First post 04/09/18,Last post 04/10/18
    Hello! I have a summary sheet that references five sheets. I already had to modify my ranges for VLOOKUP on those sheets because I hit that 25,000 cell reference within moments. Three weeks later after changing all of my sheets, I'm ready…
    Formulas and Functions171
    Laura Senff
  • Srowley
    Srowley 8 views 1 comment Most recent by Chris Campbell
    First post 04/09/18,Last post 04/10/18
    Looking for a formula that generates a renewal date. Here are my three column headers Column 7= Return Date Column 8 = Paid Through  Column 9 = Next Invoice Date I want the Next Invoice Date to be either the Return Date or the day afte…
    Formulas and Functions81
    Srowley
  • smuhammad41001
    smuhammad41001 106 views 1 comment Most recent by Chris McKay
    First post 04/09/18,Last post 04/09/18
    I have 2 columns to be used in this formula: One is for the Date Completed in Date Format, and the other is the task Completed checkbox column. I would like to create a formula that automatically enters the date as Today's Date in the Da…
    Formulas and Functions1061
    smuhammad41001
  • Rob Pritchard
    Rob Pritchard 24 views 1 comment Most recent by Shaine Greenwood
    First post 04/05/18,Last post 04/09/18
    I have created a smartsheet with a number of different formulas in to pull together monthly figures. I then use this information to populate charts in my sights. One view I use is to count the number of new jobs added each month. This was …
    Formulas and Functions241
    Rob Pritchard
  • cedwards
    cedwards 20 views 1 comment Most recent by Shaine Greenwood
    First post 04/05/18,Last post 04/09/18
    I am trying to write a formula to count the lowest level of "Completed" items.  I have multiple Projects and different Status all on the same sheet.     =COUNTIFS((CHILDREN[Variable Features/Tasks]:[Variable Features/Tasks]), …
    Formulas and Functions201
    cedwards
  • BlairMyles
    BlairMyles 7 views 1 comment Most recent by Shaine Greenwood
    First post 04/04/18,Last post 04/09/18
    Hi there, i have the following formula that i cant get to work. basically, i am raising a flag if a job is at one of multiple defined stages and withing a specific timeframe. its the multiple stages bit that i am stuck with.   current f…
    Formulas and Functions71
    BlairMyles
  • Kate
    Kate 8 views 1 comment Most recent by Shaine Greenwood
    First post 04/04/18,Last post 04/09/18
    I have created formulas to count the number of incoming quotes by month and according to if they have been Won/Lost/Pending. I use the exact same formula for March (month 3) and it works beautifully. For some reason the formula is not pull…
    Formulas and Functions81
    Kate
  • CLaughrey
    CLaughrey 55 views 1 comment Most recent by Mitch.Shaffer
    First post 04/09/18,Last post 04/09/18
    What formula can I use that simply counts the number of parent rows in a single sheet?   My scenario: Each parent row is a different customer.  I want to know how many active customers a particular PM is working with and link that number…
    Formulas and Functions551
    CLaughrey
  • Team Orbie 9
    Team Orbie 9 43 views 2 comments Most recent by Team Orbie 9
    First post 04/07/18,Last post 04/08/18
    Hello All, I am new to SS and am trying to figure out how to add these hours and minutes for the year (there will be more added each week). I have found some posts saying this is not possible but they are from last year. Has anyone master…
    Formulas and Functions432
    Team Orbie 9
  • 66Lambretta
    66Lambretta 8 views 1 comment Most recent by Chris McKay
    First post 04/07/18,Last post 04/08/18
    Hi all, Can someone either let me know or point in the direction of a post that would assist me in solving for the following.  I am trying to create a formula that would:   * Return red ball if date in a column titled "Last contact…
    Formulas and Functions81
    66Lambretta