Formulas and Functions

Formulas and Functions

Archives for October 2019

Discussion List

  • CliffG
    CliffG 232 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 Functions2326
    CliffG
  • KMan1190
    KMan1190 51 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 Functions513
    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 703 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 Functions7032
    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 Functions481
    pbfalk
  • sahilhq
    sahilhq 13 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 Functions139
    sahilhq
  • Donni Matthew
    Donni Matthew 63 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 Functions639
    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 Functions885
    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 Functions157
    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 Functions481
    sanjayb
  • Samira
    Samira 176 views 9 comments Most recent by Paul Newcome Formulas and Functions
    First post 10/30/19,Last post 04/08/20
    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 Functions1769
    Samira
  • Andrea Mayer
    Andrea Mayer 37 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 Functions378
    Andrea Mayer
  • rstrecker
    rstrecker 120 views 3 comments Most recent by Paul Newcome Formulas and Functions
    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 Functions1203
    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 Functions2037
    Nolan Nordlund
  • sonam.bang
    sonam.bang 80 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 Functions803
    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 14 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 Functions143
    Rebecca Wilhelm
  • Faz Mussa
    Faz Mussa 14 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 Functions144
    Faz Mussa
  • sahilhq
    sahilhq 40 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 Functions405
    sahilhq
  • mavalos
    mavalos 1.2K 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 Functions12328
    mavalos
  • kari1282
    kari1282 12 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 Functions121
    kari1282
  • Martin Knor
    Martin Knor 219 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 Functions2195
    Martin Knor
  • Heather K
    Heather K 172 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 Functions1723
    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 Functions275
    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 Functions1313
    NLegresley
  • NBauer
    NBauer 84 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 Functions844
    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 Functions426
    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 Functions626
    xnickeddlemanx
  • Sarah_Michael
    Sarah_Michael 11 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 Functions114
    Sarah_Michael
  • mmurray109161
    mmurray109161 216 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 Functions2162
    mmurray109161