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

  • @SPark
    Using IF, AND, ORAccepted Answer
    Accepted Answer @SPark 27 views 2 comments Most recent by Genevieve P.
    First post 01/15/20,Last post 01/16/20
    Looking for formula advice/help! I would like to write a formula that will: Mark Past Due if Complete By is greater than today + task is not complete If no Complete By date exists, than Past Due flag remains unchecked So far, I've been abl…
    Formulas and Functions272
    @SPark
  • SuzanneH
    Answered SuzanneH 25 views 8 comments Most recent by Andrée Starå
    First post 12/30/19,Last post 01/16/20
    I have a form entry sheet where I have some columns hidden but defaulting to a formula. The goal is to have the columns populate with the results of the formulas without any updates required (like cutting and pasting the formula in the gr…
    Formulas and Functions258
    SuzanneH
  • jlheaney
    Answered jlheaney 36 views 4 comments Most recent by Andrée Starå
    First post 12/27/19,Last post 01/16/20
    Hi All! I am wondering if this functionality is possible. I have created a sheet and once a row is populated is it copied to a new sheet for my employees. There is a 'Completion Date" field, I would like for them to complete when that…
    Formulas and Functions364
    jlheaney
  • Angela Ryer
    Accepted Answer Angela Ryer 25 views 2 comments Most recent by Angela Ryer
    First post 01/15/20,Last post 01/16/20
    I'd like a way to count Theme column (Text Column) if "IVR" appears, AND if the Status Column (Dropdown column) is either "Backlog" or "To Do" or "Doing" I can get a simple countif formula to work, …
    Formulas and Functions252
    Angela Ryer
  • Roshawnda Allen
    First post 01/15/20,Last post 01/15/20
    My formula is below and is returning "INCORRECT ARGUMENT". =SUMIF({Weekly Production Orders Range 2}, AND(ISBLANK(@cell) = false, AND(@cell >= DATE(2020, 1, 12), @cell <= DATE(2020, 1, 18))), [Week 1]2:[Week 1]10)
    Formulas and Functions204
    Roshawnda Allen
  • Eric Tronson
    Validation FormulaAccepted Answer
    Accepted Answer Eric Tronson 39 views 11 comments Most recent by Andrée Starå
    First post 01/11/20,Last post 01/15/20
    Going crazy. My current formula =IF(AND([Time Missed / Used (In Hours)]1 < 19.99),CONTAINS("Not", [Excused/Not Excused]1:[Excused/Not Excused]1, ".25", "1"))) = #UNPARSEABLE The goal here is to return .25…
    Formulas and Functions3911
    Eric Tronson
  • wabel adnan alzaeem
    wabel adnan alzaeem 30 views 6 comments Most recent by Paul Newcome
    First post 12/31/19,Last post 01/15/20
    In the created project management fields, there is a start and end dates for the task assigned to the person I would like to inquire about the following cases: When I delay the task completion date, for example 3 days, how can I later revi…
    Formulas and Functions306
    wabel adnan alzaeem
  • Popak Roshan
    Accepted Answer Popak Roshan 18 views 3 comments Most recent by Paul Newcome
    First post 01/13/20,Last post 01/15/20
    I am trying to drag down dates to populate year days using formulas. It does not go to the next day using drag down. Is there any trick to it? Here is the example Date1= 01/01/2020 First Cell =$Date$1 - VALUE(YEARDAY($Date$1) - 1) = 0…
    Formulas and Functions183
    Popak Roshan
  • Nataleigh
    Nataleigh 43 views 6 comments Most recent by Andrée Starå
    First post 09/27/19,Last post 01/15/20
    I have a sheet with a formula set up to find duplicate email addresses. But, when new rows are added, the formula doesn't copy to the new row. Is there a way to make the the new row also includes the formula. Sidenote: when a new row is cr…
    Formulas and Functions436
    Nataleigh
  • susannebankhead
    Accepted Answer susannebankhead 20 views 3 comments Most recent by Andrée Starå
    First post 01/11/20,Last post 01/15/20
    https://us.v-cdn.net/6031209/uploads/981/62W23XBRXL6J.jpg I'm trying to figure out the right formula to use to count multiple choices answers in one column in my sheet. I'm hoping to have this information roll up into a dashboard. But, for…
    Formulas and Functions203
    susannebankhead
  • Mike Ramirez
    First post 01/14/20,Last post 01/15/20
    I am not making any edits to any column properties and can't explain why it has changed overnight multiple times. Has anyone had this problem or know why it is happening? Thanks!
    Formulas and Functions115
    Mike Ramirez
  • Hasan Syed
    Accepted Answer Hasan Syed 21 views 2 comments Most recent by Andrée Starå
    First post 01/14/20,Last post 01/14/20
    I am having issues with only one color in my formula for RYBG coded to the statuses I have. It appears Yellow always renders and invalid but the other colors do not. What am i missing in my syntax? =IF(Status5 = "Complete", "…
    Formulas and Functions212
    Hasan Syed
  • peter65516
    Accepted Answer peter65516 10 views 3 comments Most recent by Paul Newcome
    First post 01/14/20,Last post 01/14/20
    Hi, I am trying to use a COUNTIFS formula to identify the number of cells from a column ('Age Range') with a drop-down box with various options ('Under 18' / '18 o 30' / '30+') and are from a particular year in a 'YEAR' column. I know tha…
    Formulas and Functions103
    peter65516
  • Mary Ayers
    Accepted Answer Mary Ayers 25 views 3 comments Most recent by Paul Newcome
    First post 01/14/20,Last post 01/14/20
    Hello all. I am thinking this would best be done in a Sheet Summary but my brain seems to still be in winter break mode. Hoping someone can point me in the right direction. My Smartsheet is a list of courses (parent rows including course t…
    Formulas and Functions253
    Mary Ayers
  • Kitty
    First post 01/14/20,Last post 01/14/20
    I have 8 Associate Directors who are covering different AREAS of the world. When a completed form populates my Master Sheet, can I direct it to copy that row to the correct AREA sheet according to the AREA chosen? (I would have 8 AREA she…
    Formulas and Functions173
    Kitty
  • Kristie Diersen
    Accepted Answer Kristie Diersen 20 views 3 comments Most recent by Paul Newcome
    First post 01/14/20,Last post 01/14/20
    What I am looking to do is have a field fill in Yes or No based on the Impacted Article Complete Date in the Child row and the Alert Expiration Date in the Parent Row.  If the Impacted Article complete Date is prior to the Alert Expiration…
    Formulas and Functions203
    Kristie Diersen
  • Alessandro Terranova
    First post 11/01/19,Last post 01/14/20
    Hi Community, I am trying to calculate the actual duration starting from a list of Start and End dates. What I am trying to achieve is to exclude duration overlaps in order to get the actual effort days only, but including not contiguous…
    Formulas and Functions4627
    Alessandro Terranova
  • Joselyn Davila
    Dashboard Viewer FilterAccepted Answer
    Accepted Answer Joselyn Davila 20 views 4 comments Most recent by Andrée Starå
    First post 01/13/20,Last post 01/14/20
    I attended Engage last year and a new advanced option was announced called "Dashboard Viewer." I can not find this on any of my dashboards. Is there anyway I can filter the data on by dashboard by different users viewing it? …
    Formulas and Functions204
    Joselyn Davila
  • Cong Tu
    Accepted Answer Cong Tu 15 views 3 comments Most recent by Andrée Starå
    First post 01/14/20,Last post 01/14/20
    https://us.v-cdn.net/6031209/uploads/335/68XF8LW4WXHU.png If only Financial Risk is filled out AP status should be "1 star" If GPS Recommendation are filled out AP status should also be “3 points” If all 3 successor columns are f…
    Formulas and Functions153
    Cong Tu
  • Jasmine Brown
    Accepted Answer Jasmine Brown 40 views 6 comments Most recent by Andrée Starå
    First post 12/24/19,Last post 01/14/20
    I am working on a spend tracker and i am having trouble pulling currency conversions from other cells in the row. I need the 'committed amount in USD' to pull from the correct currency conversion cell depending on the currency selected. cu…
    Formulas and Functions406
    Jasmine Brown
  • TrevorM
    TrevorM 63 views 14 comments Most recent by Andrée Starå
    First post 11/27/19,Last post 01/13/20
    We currently use Jotform for some of our forms here and would like to be able to bring more of them over to Smartsheet if possible As you can see from the attached files, the form is set up to add a new line after each previous line is fi…
    Formulas and Functions6314
    TrevorM
  • rbuckner
    Accepted Answer rbuckner 38 views 5 comments Most recent by Andrew Stewart
    First post 01/11/20,Last post 01/13/20
    I would like the flag to be red if the date is within 7 days of "today", AND, if "Status" is not "Complete", and I want the flag to be white if the "Due Date" is blank, greater than seven days away, …
    Formulas and Functions385
    rbuckner
  • Yolanda Brown-Mccutchen
    First post 01/13/20,Last post 01/13/20
    I keep getting #UNPARSEABLE when using the following formula: =IF([CUSTODY DATE]7>1/1/2000,IF($MILEAGE$2<=([CUSTODY DATE]7+2),(125+75+40),($MILEAGE$2-([CUSTODY DATE]7+2)*15+(125+75+40)), ) I was told to make this correction, however,…
    Formulas and Functions3412
    Yolanda Brown-Mccutchen
  • L@123
    [email protected] 25 views 2 comments Most recent by Paul Newcome
    First post 01/13/20,Last post 01/13/20
    Problem: I had the need to copy a list of 26 formulas to 56 sheets maintaining the formulas for each sheet. to increase the data collection across the sheets. All sheets have the same column names. Smartsheet defaults to saving the end res…
    Formulas and Functions252
    [email protected]
  • Jenna Corso
    Accepted Answer Jenna Corso 36 views 7 comments Most recent by Paul Newcome
    First post 01/13/20,Last post 01/13/20
    Hello, I have a contact column where some rows have more than one person assigned to a task. I want to count the number of tasks assigned to any given individual. This is the formula that I have created to do this: =COUNTIF(CONTAINS("…
    Formulas and Functions367
    Jenna Corso
  • khankoff
    khankoff 43 views 4 comments Most recent by Aaron Dyess
    First post 12/05/19,Last post 01/13/20
    Hi Community, I'm trying to convert some numbers expressed in KB into GB so I can sum all in GB.  To do this I had to use the LEFT function to pull everything appended with "KB" from a column.  That was no problem.  I then took …
    Formulas and Functions434
    khankoff
  • Clark Everson
    RYG FormulaAnswered
    Answered Clark Everson 22 views 1 comment Most recent by Mike Wilday
    First post 01/13/20,Last post 01/13/20
    I am trying to make RYG circles correspond to a formula. My current formula, which has anything overdue marked red, anything due today marked yellow and anything not today is green works and is as such: =IF(Finish1 < TODAY(), "Red&…
    Formulas and Functions221
    Clark Everson
  • Blaine Loos
    Answered Blaine Loos 19 views 1 comment Most recent by Andrée Starå
    First post 01/13/20,Last post 01/13/20
    I have a problem and would love to hear if anyone has a creative solution. I have a large "Master Sheet" that essentially aggregates data from hundreds of other sheets across multiple workspaces and serves as a central data repos…
    Formulas and Functions191
    Blaine Loos
  • Luke Esdale
    Answered Luke Esdale 20 views 2 comments Most recent by Ella
    First post 01/13/20,Last post 01/13/20
    Hi Smartsheet team, My team and I use smartsheet to track our business development opportunities. When we get new leads our team use forms to input the data they need which then adds a new line into our smartsheet doc. However some of the …
    Formulas and Functions202
    Luke Esdale
  • Amanda Chu
    #INCORRECT ARGUMENT SETAccepted Answer
    Accepted Answer Amanda Chu 240 views 15 comments Most recent by Andrée Starå
    First post 03/13/19,Last post 01/13/20
    Hello, I cannot figure out why this SUMIFS is not working. When I break up the SUMIF criteria, they both work fine. But when I put them together, I get #INCORRECT ARGUMENT SET error message. =SUMIFS({MH Client Profiles v2 Range 13}, &quo…
    Formulas and Functions24015
    Amanda Chu