Formulas and Functions

Formulas and Functions

Discussion List

  • michael tomlinson
    michael tomlinson 12 views 1 comment Most recent by michael tomlinson
    First post 11/18/19,Last post 11/20/19
    I'm trying to figure out a way to apply my vlookup or index/match combo as a dynamic formula across my sheet.  I have a set cell that I can use as the beginning of my array of cells to reference but I want the formula to update the final c…
    Formulas and Functions121
    michael tomlinson
  • gerry@prd
    [email protected] 71 views 3 comments Most recent by [email protected]
    First post 11/19/19,Last post 11/20/19
    I created a project management financial sheet (client / projects / deliverables & invoicing). I am trying to sum the value of invoices between a date range (screen grab).  This is what I thought would work: =SUMIF(DTI:DTI, AND(@cell…
    Formulas and Functions713
    [email protected]
  • Tony Barthelemy
    Tony Barthelemy 21 views 15 comments Most recent by Andrée Starå
    First post 11/08/19,Last post 11/20/19
    Happy Friday All: I am trying to replace IF Formula with IFERROR so I do not receive #DIVIDE BY ZERRO Error. However, I receive Incorrect when I use the IFERROR. Can anyone help? Existing Formula: =IF(AND(TODAY() >= [Start Date (Plan…
    Formulas and Functions2115
    Tony Barthelemy
  • swsmartsheet
    swsmartsheet 243 views 2 comments Most recent by swsmartsheet
    First post 11/15/19,Last post 11/20/19
    Is it possible to make a decision tree type project in smartsheet? For example, I need to decide between options A, B, and C that have different cost implications and dependencies, but also need to plan for the potential staff and cost all…
    Formulas and Functions2432
    swsmartsheet
  • Jack C
    Jack C 74 views 7 comments Most recent by Paul Newcome
    First post 11/19/19,Last post 11/20/19
    Greetings - I'm having an issue trying to flag duplicates from 2 separate columns in the same sheet. I have been able to flag duplicates in a single stand-alone column but trying to using the same formula with the "COUNTIFS" fun…
    Formulas and Functions747
    Jack C
  • AdrienneBarton
    First post 02/11/19,Last post 11/20/19
    There are about 10 blank rows at the bottom of my sheet that keep coming back, even after I delete them. I either need to know how to permanently delete them or how to adjust my formulas to not include them because they are affecting our d…
    Formulas and Functions514
    AdrienneBarton
  • Nolan Nordlund
    Nolan Nordlund 207 views 7 comments Most recent by Andrée Starå
    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 Functions2077
    Nolan Nordlund
  • mynameisc
    First post 11/14/19,Last post 11/20/19
    I want to do some logic based on whether an entry starts with a number.  I assumed `=IF(ISNUMBER(LEFT([Primary Column]1)), "Number", "No Number")` would have worked, but it seems to always return FALSE.  What am I missi…
    Formulas and Functions631
    mynameisc
  • hildegrant
    hildegrant 131 views 1 comment Most recent by Andrée Starå
    First post 11/19/19,Last post 11/20/19
    Hi there!  I am trying to set up alerts for a smartsheet document to notify any contact that does not have a specific row updated. I have two columns that I'm currently referencing in the sheet, and I'm hoping that I can have a cell retur…
    Formulas and Functions1311
    hildegrant
  • LLaCosta
    LLaCosta 31 views 3 comments Most recent by Genevieve P
    First post 11/15/19,Last post 11/20/19
    I have a Target % Complete column in a project plan template that essentially looks to see if you are tracking to the % complete you should be based on today's date and the start and end date of any task (or tells you that you are missing …
    Formulas and Functions313
    LLaCosta
  • @SPark
    @SPark 31 views 3 comments Most recent by Mike Wilday
    First post 11/19/19,Last post 11/19/19
    Hello! I'm trying to have this formula round + add a "w" to the end but I cannot seem to figure it out.  The formula is: =(Due1 - [email protected]) / 7 + "w" I would like it to say 12.9w but it keeps saying 12.85714w and I'v…
    Formulas and Functions313
    @SPark
  • bscruggs
    bscruggs 114 views 3 comments Most recent by Mike Wilday
    First post 11/19/19,Last post 11/19/19
    Hi!  I'm new to the community and already raising an issue.  Sorry about that!  If anyone can help me with this, I'd greatly appreciate it.  I'm trying to use VLOOKUP within a Product Roadmap sheet to set the values of the feature start d…
    Formulas and Functions1143
    bscruggs
  • eceisin
    eceisin 51 views 8 comments Most recent by eceisin
    First post 11/18/19,Last post 11/19/19
    Hi, I'm working matching mentors and mentees.  We would like to match them based on attributes they both selected in a multi select column.  As in, choose your top three competencies.  Now I would like to make a formula to determine the pe…
    Formulas and Functions518
    eceisin
  • AntonioR
    AntonioR 21 views 2 comments Most recent by Mike Wilday
    First post 11/19/19,Last post 11/19/19
    Hello all. I have several work flow sheets, in which collaborators have to change the status of the tasks, but not all collaborators could change any status.  The question is possible edit permissions on cells to avoid that any user coul…
    Formulas and Functions212
    AntonioR
  • Kristina Howes
    First post 11/13/19,Last post 11/19/19
    Hi SS Community!    Had a complicated question and my formula keeps coming back unparseable, so I wanted to run this by you and see if anybody could help me.  Currently, I have this:  =IF(AND([Start Date]@row < (TODAY() + 14)), NOT(…
    Formulas and Functions724
    Kristina Howes
  • Hend_Farah
    Hend_Farah 51 views 9 comments Most recent by Peggy Huang
    First post 07/29/19,Last post 11/19/19
    Hi Smartsheet Team ,  I have an issue with the Today() function not reading my date correctly . I contacted you and you provided me with a long formula to overcome this but it still didn't solve the issue for us .  The formula i'm using …
    Formulas and Functions519
    Hend_Farah
  • Marcus
    Marcus 53 views 4 comments Most recent by Marcus
    First post 11/14/19,Last post 11/19/19
    Hi guys, I am trying setting up a new report for my team which will allow them to bring through project activities based on the following criteria: * The week the activity happened in is before today's current week. * The RYG status is r…
    Formulas and Functions534
    Marcus
  • Bart13550
    First post 11/18/19,Last post 11/19/19
    I am trying to create a countifs function to count 5-star ratings by restaurant location published within the past 7 days. The published column contains dates. So far, I have this equation: =COUNTIFS(Rating:Rating, =5, Location:Location, …
    Formulas and Functions111
    Bart13550
  • edwardnsantos
    edwardnsantos 51 views 1 comment Most recent by Andrée Starå
    First post 11/18/19,Last post 11/19/19
    Hey Community -  I am having an issue trying to use the COUNTIF formula. In this use case, I basically have a formula in the first column to show if start and end dates match with other columns in the sheet. All I want to do is count the…
    Formulas and Functions511
    edwardnsantos
  • marwinrj
    marwinrj 51 views 2 comments Most recent by marwinrj
    First post 11/06/19,Last post 11/18/19
    Hello,  I'm fairly new to smartsheets and trying to solve a roadblock.  I'm trying to count the amount of times a specific string is in a cell with two types of status: closed and submitted. I'm using the FIND because some cells c…
    Formulas and Functions512
    marwinrj
  • dsmartsheetuser
    dsmartsheetuser 82 views 1 comment Most recent by Frank Falco
    First post 11/18/19,Last post 11/18/19
    Hi,  I am trying to get a count of all Task Named: "To Do", where "Start Date" is less than Today's date. I am using: COUNTIFS(Task Name, "To Do", StartDate, "StartDate < Today()"), for this, I …
    Formulas and Functions821
    dsmartsheetuser
  • Terrell.Boaz52986
    First post 11/15/19,Last post 11/18/19
    Hi, I want to create a formula in a date column that allows me to count all of the cells in the column that are not blank but do not contain a date. I have tried various COUNTIFS but can not seem to find the perfect solution. Thank you fo…
    Formulas and Functions933
    Terrell.Boaz52986
  • Lindseyh111241
    Lindseyh111241 11 views 1 comment Most recent by Eric M Oliveira
    First post 11/14/19,Last post 11/18/19
    Hi,  I have a sheet that tracks a group of teams' weekly KPI's. Each week on a Thursday, the different team members need to access their sheet, update the date that their KPI's relate to and enter the KPI's for each line. Each team has a …
    Formulas and Functions111
    Lindseyh111241
  • Darren I
    Darren I 21 views 3 comments Most recent by Genevieve P
    First post 11/11/19,Last post 11/18/19
    Hello, Does anybody know if there is there a way to make the below formula work? I'm not sure what I'm missing. I need a count of children where the difference between the task due date and the created date is 1 or less.This one has stum…
    Formulas and Functions213
    Darren I
  • Jamie Null
    Jamie Null 42 views 1 comment Most recent by Genevieve P
    First post 11/14/19,Last post 11/18/19
    I have a column in another sheet called Ranking (Text/Number field).  It contains numbers from 1-200.  Using this formula it's returning a value of 0 which is not accurate from the column I selected. Is there something I'm missing? =COUNT…
    Formulas and Functions421
    Jamie Null
  • matthew.hird90386
    matthew.hird90386 11 views 1 comment Most recent by Andrée Starå
    First post 11/17/19,Last post 11/18/19
    Hi: I have a sheet (target) which has a project ID. I have another sheet  (source) with 100 columns (which has the project id)  I want In the target to take about 20 fields from the source and put into the target. Vlookup is limited as…
    Formulas and Functions111
    matthew.hird90386
  • Witherwax
    Witherwax 53 views 1 comment Most recent by Genevieve P
    First post 11/11/19,Last post 11/17/19
    =IFERROR(SUMIFS({# tags}, {TaggedBy}, Employee1, {QAedCebu}, MONTH(@cell) = 10), " ") Formula Breakdown: (Reference Sheet A contains all tags, dates and employee assignments, Sheet B is the sheet I am trying to create totals on)…
    Formulas and Functions531
    Witherwax
  • Jon Stikka
    Jon Stikka 21 views 5 comments Most recent by Andrée Starå
    First post 11/12/19,Last post 11/15/19
    I am trying to write a formula for a sheet summary where an "Over Due" check box is checked (driven by a due date column), and a "Done" check box is not checked... =COUNTIFS([Over Due]:[Over Due], 1, Done:Done, 0) #In…
    Formulas and Functions215
    Jon Stikka
  • briparke@
    [email protected] 32 views 3 comments Most recent by Mike Wilday
    First post 11/15/19,Last post 11/15/19
    Hello having trouble creating a formula and not sure if it is possible So I have two sheets first with 5 check boxes and then a value field. The second which holds the values. I want to be able to check the boxes and then have the value …
    Formulas and Functions323
    [email protected]
  • steven.nienaber105336
    First post 11/14/19,Last post 11/15/19
    I have a sheet that houses all projects within one department.  Many of the projects have child rows that I don't want to include in my count formula.  I have a Hierarchy column that reflects "0" in the parent rows - those are th…
    Formulas and Functions121
    steven.nienaber105336