Formulas and Functions

Formulas and Functions

Discussion List

  • terri.white82166
    terri.white82166 5 views 2 comments Most recent by Paul Newcome
    First post 03/18/19,Last post 03/21/19
    I want "In progress" to display if the check # field is blank or CCC Approved field is Yes, otherwise I want "Complete" to display.    I've tried this formula but the syntax is wrong. =IF(ISBLANK([Check #]1), "In …
    Formulas and Functions52
    terri.white82166
  • Lota
    Lota 132 views 4 comments Most recent by Lota
    First post 11/06/18,Last post 03/21/19
    Hello Everyone, I am trying to write an IF function to automatically change the value of a certain cell for certain conditions. For some reason I keep getting the #Unparseable error when I type out this formula. =IF((NETDAYS([Date Need B…
    Formulas and Functions1324
    Lota
  • Dartwohl
    Dartwohl 112 views 6 comments Most recent by Andrée Starå
    First post 03/18/19,Last post 03/21/19
    I'm using the following formula to piece together a date in a text/number cell: =IF(MONTH([Sale Date Form]@row) < 10, "0") + MONTH([Sale Date Form]@row) + "-" + IF(DAY([Sale Date Form]@row) < 10, "0") +…
    Formulas and Functions1126
    Dartwohl
  • Amanda Chu
    Amanda Chu 103 views 2 comments Most recent by Paul Newcome
    First post 03/18/19,Last post 03/21/19
    I am having #INCORRECT ARGUMENT SET error message with this COUNTIFS. Please help.  I am trying to Count Client Name column if both criteria are met.  1) Client Experience Manager = "value" 2) Terminated checkbox not CHECKED …
    Formulas and Functions1032
    Amanda Chu
  • LMK
    LMK 93 views 2 comments Most recent by Paul Newcome
    First post 03/19/19,Last post 03/21/19
    Can someone please assist with reporting at risk line items?  I have parent/child hierarchy and want to only report those parents that are over budget and only the line items under that parent that have fees greater than 0. (Image)
    Formulas and Functions932
    LMK
  • vasiles.kiosses75516
    First post 03/19/19,Last post 03/21/19
    Hi ,  I am struggling a bit with using Countif to return a value from another sheet. A column contains numbers, I want to return number of cells that are for example >5<=10, greater than 5 but less than or equal to 10. For example =…
    Formulas and Functions2723
    vasiles.kiosses75516
  • alexmans
    alexmans 28 views 2 comments Most recent by Paul Newcome
    First post 03/20/19,Last post 03/21/19
    Hi there, I have a column that shows a set of values like this:              COLUMN A Row 1   20:40:0:0:20:0 Row 2   2:4:0:0:0:4 Row 3 .  0:0:0:0:0:0 etc. Each row represents a project task. The value represents the number of e…
    Formulas and Functions282
    alexmans
  • Ann S
    Ann S 1 view 6 comments Most recent by Andrée Starå
    First post 03/19/19,Last post 03/21/19
    Hello! I am trying to write a formula to count whether a group is active or inactive and how many groups are active. There are a couple conditions though, column1: the cell must not be blank (if it blank, it should not count), column2 **i…
    Formulas and Functions16
    Ann S
  • BESP10
    BESP10 14 views 7 comments Most recent by Andrée Starå
    First post 03/18/19,Last post 03/20/19
    Good evening, I will try and explain this without messing up * We are using smartsheet to track fabrication of product * each row is an item we fabricate * each item has a budgeted amount of time per task designated in a column * they ar…
    Formulas and Functions147
    BESP10
  • emailmike1994
    emailmike1994 378 views 6 comments Most recent by LGustin
    First post 08/02/18,Last post 03/20/19
    I have two sheets, one where I had a form that lets people input safety scores a row at a time for a given construction job site and another where I'm looking to summarize all the safety scores for a particular job site overall, today, thi…
    Formulas and Functions3786
    emailmike1994
  • handmadetsunami
    handmadetsunami 13 views 11 comments Most recent by Paul Newcome
    First post 03/15/19,Last post 03/20/19
    Hi,   I have a sheet that currently takes a start date and adds a number of years from another column to create an end date: =IF(OR(ISBLANK([Contract Start Date]60)), "", IF(OR(ISBLANK([Contract Period (Years)]60)), "&quo…
    Formulas and Functions1311
    handmadetsunami
  • Ezra
    Ezra 699 views 3 comments Most recent by [email protected]
    First post 03/19/19,Last post 03/19/19
    I was working on a way to track some task hours and minutes... came up with this: One of the problems I ran into is that if you put "00" (double-ought) or any minute with a preceding zero, it would error. You can see the work-ar…
    Formulas and Functions6993
    Ezra
  • Dee
    Dee 13 views 1 comment Most recent by Shaine Greenwood
    First post 03/15/19,Last post 03/19/19
    Hi Smartsheet community, As an Admin, I setup a dashboard with a chart showing data with $ signs. A viewer sees the same dashboard chart with US$ signs.   Is there a way for the viewer to see only $? Thanks for any suggestions! Dee
    Formulas and Functions131
    Dee
  • waygjlon
    waygjlon 76 views 3 comments Most recent by Andrée Starå
    First post 03/01/19,Last post 03/19/19
    I am having trouble working out a formula to put into a cell. I want the cell to change, based on the value in a cell.  If the 'status' cell in my document is marked as 'complete', I want to change a cell at the end of the document to 'Co…
    Formulas and Functions763
    waygjlon
  • khankoff
    khankoff 4 views 6 comments Most recent by Andrée Starå
    First post 03/15/19,Last post 03/19/19
    I have a list of records in a worksheet, and would like for each of them to be prioritized 1 through ### (depending upon how many are in the list).  Each record needs to have a unique priority, because the order in which the record will be…
    Formulas and Functions46
    khankoff
  • Resalat Kabir
    Resalat Kabir 36 views 3 comments Most recent by Andrée Starå
    First post 03/18/19,Last post 03/19/19
    Dear all  I deal with importing goods from all over the world, and payment usually happens in multiple currencies. In smartsheet, I have a column for currency (USD/Euro etc) and the next column I have the amount. What I am looking for i…
    Formulas and Functions363
    Resalat Kabir
  • EFHuey
    EFHuey 101 views 5 comments Most recent by chasila
    First post 08/24/18,Last post 03/18/19
    I am working on creating a master list of events, that pulls data from 2 source sheets. I worked out the linking, but when the source sheets are re-sorted it messes up my links on the destination "master list". Is there a way aro…
    Formulas and Functions1015
    EFHuey
  • kari1282
    kari1282 2 views 1 comment Most recent by [email protected]
    First post 03/18/19,Last post 03/18/19
    I have this formula to give me the sumif for multiple criteria I was just wondering if there is a simpler formula =SUMIF({Healtcare Range 3}, @cell = "Memberships", {Healtcare Range 2}) + SUMIF({Healtcare Range 3}, @cell = "…
    Formulas and Functions21
    kari1282
  • grant53396
    grant53396 12 views 6 comments Most recent by grant53396
    First post 03/15/19,Last post 03/18/19
    Hi, I currently have the following formula which is used for counting how many jobs we intend to complete on a day: =COUNTIF({Realistic Completion Date 4}, {Realistic Completion Date 4} = TODAY()) This works fine, although there is a sc…
    Formulas and Functions126
    grant53396
  • Veronica J
    Veronica J 37 views 5 comments Most recent by Paul Newcome
    First post 03/15/19,Last post 03/18/19
    Hello, Does anyone know how I can use the GYR symbols in a nested if condition? I am using three columns, the last one using the symbol to reflect the status. Here is my logic: If column A has no text, show in column C nothing or "&…
    Formulas and Functions375
    Veronica J
  • Mary Ayers
    Mary Ayers 768 views 4 comments Most recent by Mary Ayers
    First post 03/03/19,Last post 03/16/19
    Hi all, I am trying to build a combined IF statement for a date validation field. Both of these statements work on their own. The first statement returns "University Holiday" if the "Preferred Date" is a holiday... =…
    Formulas and Functions7684
    Mary Ayers
  • nicole.m
    First post 03/15/19,Last post 03/16/19
    I would like to track the number of work days an item is in each status. For example, an item was in Feasibility Assessment status for 3 days and UAT status for 8 days. Is there a way to do this in Smartsheet without using a third party to…
    Formulas and Functions134
    nicole.m
  • AndreaM
    AndreaM 50 views 1 comment Most recent by Kara Lumley
    First post 03/15/19,Last post 03/15/19
    I've been importing data from Toggl (toggl.com) using Zapier (zapier.com) to SmartSheet. The date/times are recorded in Toggl in UTC, and I'm trying to convert this to HST (i.e. -10 hrs). My settings are for HST. The UTC format: 2019-03-1…
    Formulas and Functions501
    AndreaM
  • kblack
    kblack 27 views 1 comment Most recent by Kara Lumley
    First post 03/15/19,Last post 03/15/19
    Hi everyone, I have a project that spans multiple years and contains many workflows.  I would like to be able to track the critical path to different milestones (not just the last task in the sheet).  Are there any tricks to doing this in …
    Formulas and Functions271
    kblack
  • Mandi
    Mandi 25 views 4 comments Most recent by [email protected]
    First post 03/15/19,Last post 03/15/19
    Hi  I'm trying to convert an Excel formula to Smartsheet formula: Excel formula =[Installation date]4+(365*[Warranty Expiry]$3) Aim is to give a future date when the warranty will expire x years after the piece was installed.   Thanks 
    Formulas and Functions254
    Mandi
  • kari1282
    kari1282 12 views 3 comments Most recent by Paul Newcome
    First post 03/15/19,Last post 03/15/19
    Hi I'm trying to count the items in the in one column if they are not equal to criteria in another column. May someone assist. This is what I have so far. =COUNTIFS({FDA Submission 2019 Range 1}, "DE Novo", {FDA Submission 2019 …
    Formulas and Functions123
    kari1282
  • Sarah.Humpal82041
    Sarah.Humpal82041 24 views 1 comment Most recent by [email protected]
    First post 03/15/19,Last post 03/15/19
    Wondering if it is possible to change a cell to "0" if the checkbox is checked. I'm assuming a formula will need to be used since I was unable to find it in the conditional formatting.  Thank you for your help in advance. 
    Formulas and Functions241
    Sarah.Humpal82041
  • Tori Peregoy
    Tori Peregoy 1 view 2 comments Most recent by Paul Newcome
    First post 03/14/19,Last post 03/15/19
    May I please get assistance with a formula? I am trying to do a formula that references another sheet (Loaner Sign Out Sheet), which calculates a total from that onto my Request Metrics sheet. Please see attached. I would like a total coun…
    Formulas and Functions12
    Tori Peregoy
  • Burt L
    First post 03/14/19,Last post 03/15/19
    We are building estimate sheets using a hierarchy based on Construction Specifications Institute's organizational coding system.  I have rows nested using multiple parent / children.  The top level is the CSI Division, which has children r…
    Formulas and Functions1952
    Burt L
  • EricMart
    EricMart 527 views 3 comments Most recent by Paul Newcome
    First post 03/14/19,Last post 03/15/19
    Hey folks, Thought I'd reach out to see if anyone could assist with my IF(INDEX formula. The columns involved with this one are Today's Date, Date2(abbreviated name for the day), daily sales qty's. I have a Dashboard where I return/displa…
    Formulas and Functions5273
    EricMart