Formulas and Functions

Formulas and Functions

Archives for October 2019

Discussion List

  • CliffG
    CliffG 73 views 6 comments Most recent by Andrée Starå Formulas and Functions
    First post 10/31/19,Last post 12/20/19
    I have been trying to build a formula that will find duplicate numbers. Most solutions that I found in the community will only work for text. Can anyone help with finding what I'm doing wrong. The formula shown on my screenshot is the same…
    Formulas and Functions736
    CliffG
  • KMan1190
    KMan1190 27 views 3 comments Most recent by Genevieve P Formulas and Functions
    First post 10/31/19,Last post 11/06/19
    I'd like to create a formula which only counts the Rows in which a date in a particular column is in the last 30 days (among other criteria) =COUNTIFS({Referral Source}, "Elm", {Database Status}, "Green", {Database D…
    Formulas and Functions273
    KMan1190
  • JNull
    JNull 14 views 6 comments Most recent by Andrée Starå Formulas and Functions
    First post 10/31/19,Last post 11/06/19
    I keep getting an error when writing this formula.  Can someone please help? I want to sum the "Weighted Range" when "Prob Closing" is checked (1), and "Close Date" is January (1) of 2020. The 3 columns are …
    Formulas and Functions146
    JNull
  • kim.wade56346
    kim.wade56346 293 views 2 comments Most recent by Paul Newcome Formulas and Functions
    First post 10/31/19,Last post 11/05/19
    Can anyone help me with a formula for a multi-select column where I am counting the number of occurrences of two of the options?  I think it would be a COUNTIFS(CONTAINS(OR( formula, but can't seem to get it to work. Thanks!
    Formulas and Functions2932
    kim.wade56346
  • pbfalk
    First post 10/31/19,Last post 10/31/19
    In Excel we have the ability to use custom formula for conditional formatting, is this possible in SS? Please see screenshot below.   I need to compare column values to the value at the top cell.  I can do this by adding more columns and…
    Formulas and Functions371
    pbfalk
  • sahilhq
    sahilhq 11 views 9 comments Most recent by Paul Newcome Formulas and Functions
    First post 10/31/19,Last post 11/22/19
    Hi,    Can anyone suggest me with a formula to carry out the following function: - If RISK(H/M/L) CAT (0,1,2,3) column states "2" then Start on site date column needs to insert a date 12 weeks prior to the design required date…
    Formulas and Functions119
    sahilhq
  • Donni Matthew
    Donni Matthew 38 views 9 comments Most recent by Paul Newcome Formulas and Functions
    First post 10/31/19,Last post 03/10/20
    I am using this simple formula in a new field in Sheet Summary & I keep getting an "Unparseable" Error. =COUNTIF(RESPONSIBLE ENTITY:RESPONSIBLE ENTITY, "FIRM")  
    Formulas and Functions389
    Donni Matthew
  • bday2329
    First post 10/31/19,Last post 11/01/19
    I am trying to do a =SUMIF into a sheet via cross reference (=SUMIF({Mth/Yr}, ={Prev Yr Mth/Yr}, {DofC NN.NN})) Mth/Yr = 9-2019 Prev Yr Mth/Yr = 9/2019 DofC NN.NN contains rows that have a value with 0.nn (nn = 01 thru 99) The result f…
    Formulas and Functions435
    bday2329
  • nbannwart10
    First post 10/31/19,Last post 11/01/19
    Hello! I am needing to make a widget in a dashboard t show the number of "complete, not started, or in progress" items I have. I know I need to change the status to a number. I have tried to do this with "IF logic but am fai…
    Formulas and Functions117
    nbannwart10
  • sanjayb
    First post 10/30/19,Last post 10/31/19
    I am struggling to get the formulas to retrieve a value in a multi-select cell. For example: Flavor options in multi-select dropdown: Vanilla, Chocolate, Mint Flavor1: Vanilla  (row 1) Flavor2: Vanilla, Chocolate  (row 2) Flavor3; Vani…
    Formulas and Functions381
    sanjayb
  • Samira
    First post 10/30/19,Last post 11/02/19
    I have a dashboard chart widget that was pulling data from a single select option. I've now changed it to multi-select but I see that my chart isn't pulling in that item at all when there are more than 1 item selected. Am I missing somethi…
    Formulas and Functions494
    Samira
  • Andrea Mayer
    Andrea Mayer 25 views 8 comments Most recent by Paul Newcome Formulas and Functions
    First post 10/30/19,Last post 10/31/19
    Hello - I am trying to figure out if there is a way to automate the selection of one of three stages (Not Started, In Progress, Completed) based on Start and Due date columns.  Activities with a Start Date in the future would be 'Not Sta…
    Formulas and Functions258
    Andrea Mayer
  • rstrecker
    First post 10/30/19,Last post 11/05/19
    I have been trying to use the Index & Match function to automatically populate in the Parent row based on the status of a Child row, however, the formula will not pick up the Child status. For example, I want the STATUS column of the …
    Formulas and Functions623
    rstrecker
  • Nolan Nordlund
    First post 10/30/19,Last post 11/20/19
    My organization recently moved from Google sheets to Smartsheet. We were tracking the number of jobs completed by each team member in the last week. We had multiple groups of jobs that would be uploaded as a new tab on the sheet. The repor…
    Formulas and Functions977
    Nolan Nordlund
  • sonam.bang
    sonam.bang 40 views 3 comments Most recent by Paul Newcome Formulas and Functions
    First post 10/30/19,Last post 10/31/19
    Is there a way I can give a date range in the formula below so that it averages between dates 8/21/19 - 10/29/19 =AVG(COLLECT({Nexus Dependencies Range 5}, {Nexus Dependencies Range 1}, "The Scrum Lords", {Nexus Dependencies Ran…
    Formulas and Functions403
    sonam.bang
  • Brian_C
    Brian_C 13 views 1 comment Most recent by Paul Newcome Formulas and Functions
    First post 10/30/19,Last post 10/30/19
    Can anyone provide a formula that calculates when an employee turns 18, based on a DOB (date of birth) cell.  I've reviewed the forum and found formulas that return an age, but we need a date so we can set up a notifier email to alert payr…
    Formulas and Functions131
    Brian_C
  • Rebecca Wilhelm
    Rebecca Wilhelm 12 views 3 comments Most recent by Paul Newcome Formulas and Functions
    First post 10/30/19,Last post 10/30/19
    I'm having issues with making this formula work for the RYGB Balls. Any glaring mistakes? -Green – An open request that was received 1-5 days ago. -Yellow – An open request that was received 6-10 days ago.  -Red – An open request th…
    Formulas and Functions123
    Rebecca Wilhelm
  • Faz Mussa
    Faz Mussa 13 views 4 comments Most recent by Mike Wilday Formulas and Functions
    First post 10/30/19,Last post 10/31/19
    I am trying to set up a formula to allow for conditional formatting but having some problems.  This is the formula I'm trying: =IF([Actual]19 >= [Target]19, IF([Actual]19 < SUM([Target]19 * 0.1), IF([Actual]19 > SUM([Target]19 *…
    Formulas and Functions134
    Faz Mussa
  • sahilhq
    sahilhq 28 views 5 comments Most recent by Andrée Starå Formulas and Functions
    First post 10/30/19,Last post 11/04/19
    Hi,    I have a Form/Dashboard on Smartsheet to book site induction on the project I am currently working on (see attached image). Just like every other Smartsheet forms, once the form is filled in it feeds into the sheet. Is it possible…
    Formulas and Functions285
    sahilhq
  • mavalos
    mavalos 304 views 8 comments Most recent by Paul Newcome Formulas and Functions
    First post 10/30/19,Last post 10/30/19
    Hello everyone, I created a checkbox column with 3 cells set to toggle on and off conditional formatting rules. My idea is to paint certain cells if certain conditions are met AND one of the 3 cells is checked.  I'm doing this mainly fo…
    Formulas and Functions3048
    mavalos
  • kari1282
    kari1282 14 views 1 comment Most recent by Paul Newcome Formulas and Functions
    First post 10/29/19,Last post 10/30/19
    Hi I'm trying to auto calculate the date. I was wondering if someone could assist. I'm not quite sure how to do it I believe it should be an if statement but I'm not sure how to write it. I have a column that is Date of Last Correspondenc…
    Formulas and Functions141
    kari1282
  • Martin Knor
    Martin Knor 80 views 5 comments Most recent by Andrée Starå Formulas and Functions
    First post 10/29/19,Last post 10/29/19
    Hey guys, I've been unable to find a solution for this one. I have a sheet from which I "VLOOKUP" values from another sheet. This another sheet gets its values from a FORM input. When I reference another sheet as a lookup_tabl…
    Formulas and Functions805
    Martin Knor
  • Heather K
    Heather K 53 views 3 comments Most recent by Paul Newcome Formulas and Functions
    First post 10/29/19,Last post 10/30/19
    I have a team task list sheet that has a drop-down Status column including Not Started, In Progress, Urgent, On Hold and Completed and we use card view for this. I have a % Complete column that is a text/number field and a Progress symbol …
    Formulas and Functions533
    Heather K
  • Cwall052
    First post 10/29/19,Last post 10/30/19
    hi,   I am using a lookup formula to find prices for specific things. once a form is used and new information is inputted into the top row the formula says unparseable and the [column name]1 changes to [column name]2. I would like to sta…
    Formulas and Functions245
    Cwall052
  • NLegresley
    First post 10/29/19,Last post 11/14/19
    Hello   I am looking for a formula that will tell me how many times a certain criteria comes up per month. We have a quote log that has different stages and we need to know how many of each type of quote stage exist per each month.  In …
    Formulas and Functions1713
    NLegresley
  • NBauer
    NBauer 40 views 4 comments Most recent by Mike Wilday Formulas and Functions
    First post 10/29/19,Last post 10/29/19
    I can't seem to get the right formula together in my Active Projects Master Schedule to show the Delta/Slippage in the construction schedules. I have individual columns for Original Finish (Date), Current Projected Finish (Date), and Chan…
    Formulas and Functions404
    NBauer
  • MJeffers
    First post 10/29/19,Last post 11/06/19
    Hello, I have a master vendor list sheet that contains a checkbox column for vendor type.  I would like to create a new sheet that pulls over just the vendors that have the vendor type column checked.  I need a separate sheet so I can use…
    Formulas and Functions296
    MJeffers
  • xnickeddlemanx
    First post 10/29/19,Last post 11/13/19
    Hi, Due to the limited restraints on the # of rows (5,000) in Smartsheet - I need to create a formula that references multiple sheets for a match statement.  See below: =IFERROR(IF(MATCH(([Employee ID]1 + [Current Position]1 + [Training …
    Formulas and Functions336
    xnickeddlemanx
  • Sarah_Michael
    Sarah_Michael 12 views 4 comments Most recent by Andrée Starå Formulas and Functions
    First post 10/28/19,Last post 10/29/19
    Help! I need to create a formula to display a Budget amount for a particular team.   The Team column is a Count Column, while the Budget column would be a sum. Can i do this? (eg: i want to know the budget # associated with a function le…
    Formulas and Functions124
    Sarah_Michael
  • mmurray109161
    mmurray109161 75 views 2 comments Most recent by Andrée Starå Formulas and Functions
    First post 10/28/19,Last post 10/29/19
    Hello -  I am displaying some information regarding things that are approved, in queue or denied. But I am needing to only count parent rows and not the children for them.  So the data collected are just for the overall brand and not sub-…
    Formulas and Functions752
    mmurray109161