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

  • taylor.thompson89836
    Accepted Answer taylor.thompson89836 25 views 5 comments Most recent by Andrée Starå
    First post 01/09/20,Last post 01/09/20
    I have a sheet where we want to show if the columns successor 1, 2, and 3 are blank the status column should be red. If only successor 1 is filled out status should be yellow If successor 1 and 2 are filled out staus should also be yellow …
    Formulas and Functions255
    taylor.thompson89836
  • RK
    Accepted Answer RK 24 views 10 comments Most recent by Paul Newcome
    First post 01/09/20,Last post 01/09/20
    Community, I'm struggling to indicate which months a project is active given a project start and end date. If a project starts on 09 January 2020 and ends on 09 March 2020, it would be active January, February, March. I would like to repre…
    Formulas and Functions2410
    RK
  • David Swearingen
    Answered David Swearingen 26 views 11 comments Most recent by Paul Newcome
    First post 01/09/20,Last post 01/09/20
    We're evaluating Smartsheet for project management. The business is commercial real estate. Projects involve matching a Client firm to a specific Property address, and that property also has an Owner. So if we have a Projects sheet, a C…
    Formulas and Functions2611
    David Swearingen
  • Stavros_McGillicuddy
    Accepted Answer Stavros_McGillicuddy 17 views 3 comments Most recent by Paul Newcome
    First post 01/09/20,Last post 01/09/20
    I am trying to create a formula that will run VLOOKUP if a cell is blank This doesn't error but it leaves the formula field blank =IF((USERR3 <> ""), (IF((VLOOKUP(SDR3, {SFDCUsers Range 1}, 2, false)) = 0, "No", &…
    Formulas and Functions173
    Stavros_McGillicuddy
  • Christina Riot
    Accepted Answer Christina Riot 26 views 4 comments Most recent by Andrée Starå
    First post 01/09/20,Last post 01/09/20
    I have a sheet tracking a project wherein the status will be changed multiple times depending on the phase of the project. Right now I'm trying to set up an autofill of the status to say "Director Approval" when the approval wor…
    Formulas and Functions264
    Christina Riot
  • L@123
    Accepted Answer [email protected] 29 views 1 comment Most recent by [email protected]
    First post 01/09/20,Last post 01/09/20
    I know this has been asked before, but I couldn't find the answer searching for it so any help would be appreciated. I have 2 separate sheets, one of them is a list of tasks to due, and another is tasks that should get added on to the firs…
    Formulas and Functions291
    [email protected]
  • Kirstine
    TagsAccepted Answer
    Accepted Answer Kirstine 36 views 16 comments Most recent by Andrée Starå
    First post 01/06/20,Last post 01/09/20
    Could someone add Workflow(s) or Automation to the tag options...? 😀
    Formulas and Functions3616
    Kirstine
  • Sarah McMorrow
    Answered Sarah McMorrow 33 views 6 comments Most recent by Paul Newcome
    First post 01/07/20,Last post 01/09/20
    Hoping someone can help here! I have rows that are parent, child and grandchild. The formula in the child row to give a percentage of grandchild that are complete is as follows: =ROUND(COUNTIF(CHILDREN(), 1) / COUNT(CHILDREN()) * 100) +…
    Formulas and Functions336
    Sarah McMorrow
  • dskpetersen
    Accepted Answer dskpetersen 21 views 5 comments Most recent by dskpetersen
    First post 01/08/20,Last post 01/09/20
    I have an auto function to move a row to different sheet based on the status. If the status changes from "Active Bid" to "No Bid" the row should move. Has always worked before but not working today? Has something cha…
    Formulas and Functions215
    dskpetersen
  • Josh Robichaud
    Answered Josh Robichaud 54 views 2 comments Most recent by Josh Robichaud
    First post 01/08/20,Last post 01/09/20
    Hello, I have a column in my sheet that is populated by a multi-select drop down in a form ("Violation" column below). Once submitted, the cells in this row will contain at least one, but typically multiple entries from the mult…
    Formulas and Functions542
    Josh Robichaud
  • katfooddfw
    Answered katfooddfw 32 views 9 comments Most recent by Paul Newcome
    First post 01/02/20,Last post 01/09/20
    I have a sheet with multiple triggers. Form populates row and assigns a Row ID Client receives a request to update info on that row Once completed, a user is notified to print out the responses for distribution I had to create a work arou…
    Formulas and Functions329
    katfooddfw
  • Bill Lyman
    Answered Bill Lyman 17 views 1 comment Most recent by Andrew Stewart
    First post 01/09/20,Last post 01/09/20
    EX: =SUM({MRRWaterfall - Smartsheet Range 1}) "Smartsheet Range "1 is the name that Smartsheet assigned to a column that I selected that is titled "Jan2015". How do I get the formula to read =SUM({MRRWaterfall - [Jan20…
    Formulas and Functions171
    Bill Lyman
  • Pam Grant
    Answered Pam Grant 16 views 1 comment Most recent by Andrew Stewart
    First post 01/09/20,Last post 01/09/20
    I want to create an agenda with start times. I want to specify the start time for the fist item and then have Smartsheet calculate the start times of the remaining items based on how many minutes are assigned to the previous item. Any ide…
    Formulas and Functions161
    Pam Grant
  • chadedwardconway
    chadedwardconway 62 views 3 comments Most recent by Mike Wilday
    First post 09/09/19,Last post 01/08/20
    Hi There! I am creating a formula that will assign contacts to a row based on user role plus some other attributes. I am finding the look up is working great for assigning 1 contact, but when I extend the formula to include multiple v-lo…
    Formulas and Functions623
    chadedwardconway
  • christy.thompson106676
    First post 10/10/19,Last post 01/08/20
    Hi - I need a formula to update the status on the parent row based on status values in the child rows below.  I am wresting with this formula and hoping someone on here can point me in the right direction.  I found this post which is exact…
    Formulas and Functions4611
    christy.thompson106676
  • Shawn Borror
    Answered Shawn Borror 23 views 3 comments Most recent by Paul Newcome
    First post 01/08/20,Last post 01/08/20
    I want to count number of projects on hold AND change control = resource constraints This is what I"ve tried; =COUNTIFs([Portfolio Rollups Range 8], "Hold", {Portfolio Rollups Range 9}, "Resource Constraints")
    Formulas and Functions233
    Shawn Borror
  • Sarah Krueger
    First post 01/08/20,Last post 01/08/20
    I want to calculate the following and have tried IF/Then formulas - but get "unparceable" every time. not started = 0% in progress = 50% complete = 100% The columns I have (Task, Phase, Milestone, Start date, End Date, Responsi…
    Formulas and Functions251
    Sarah Krueger
  • Bryan Harris
    Accepted Answer Bryan Harris 43 views 13 comments Most recent by Paul Newcome
    First post 12/17/19,Last post 01/08/20
    I am wandering the best way to create an average number of tickets entered per day, but only if at least one ticket was entered that day, for a rolling 12 months based upon a creation date. I am so new to Smartsheet that this has me all tw…
    Formulas and Functions4313
    Bryan Harris
  • Lauren Strang
    Accepted Answer Lauren Strang 29 views 5 comments Most recent by Genevieve P.
    First post 01/03/20,Last post 01/08/20
    I have a construction timeline that has tasks that lead up to it. The top Due Date is when the project needs to be completed by. The Days From Open need to tell me when to stat the task. (ex. first task start 90 days from 1/3/20). I also w…
    Formulas and Functions295
    Lauren Strang
  • steve50951
    Accepted Answer steve50951 26 views 3 comments Most recent by Paul Newcome
    First post 01/06/20,Last post 01/08/20
    Here is the formula i started with. It gives me a #unparceable response =SUMIFS(Amount:Amount, [Expense Category]:[Expense Category], [email protected]), Date:Date, YEAR(@cell) = YEAR(TODAY())) Ive included a screen shot https://us.v-cdn.net…
    Formulas and Functions263
    steve50951
  • Molly Morton
    Accepted Answer Molly Morton 18 views 1 comment Most recent by lmarchisio
    First post 01/08/20,Last post 01/08/20
    I have a question about workspace and folders and reports. I have a company workspace with 3 folders in it; Active jobs, cancelled jobs and completed jobs. I have a report that pulls from the active jobs folders, but when I create a new sh…
    Formulas and Functions181
    Molly Morton
  • Ray B
    IFErrorAccepted Answer
    Accepted Answer Ray B 37 views 14 comments Most recent by Paul Newcome
    First post 01/07/20,Last post 01/08/20
    Good Day Smartsheet Community: Can I add IFError to this Formula? I tried several times but I keep getting an incorrect or Unparseable =ROUND(AVGW(CHILDREN([% Complete (Planned)]3), CHILDREN(Duration3)), 2) Thanks
    Formulas and Functions3714
    Ray B
  • Ben Handel
    Accepted Answer Ben Handel 33 views 3 comments Most recent by Paul Newcome
    First post 01/06/20,Last post 01/08/20
    Hi, I'm trying to get my dashboard on employee training to automatically remove rows from a report when a specific user responds using a form. I have the responses from the form automatically moving to another sheet/report, but then I wa…
    Formulas and Functions333
    Ben Handel
  • Dale Heincelman
    Accepted Answer Dale Heincelman 40 views 12 comments Most recent by Dale Heincelman
    First post 01/02/20,Last post 01/08/20
    If I have a multiple selection column, like 'assigned to', where I can select some or all of the people, can I determine which specific people from the list have been selected (without using FIND or CONTAINS and looking for names one at t…
    Formulas and Functions4012
    Dale Heincelman
  • SuzanneH
    Accepted Answer SuzanneH 25 views 3 comments Most recent by Paul Newcome
    First post 01/06/20,Last post 01/08/20
    Hi, I am trying to average a range of cells. Two questions: Most important - in some cases the cell is empty. That should be treated as a zero but the AVG function is ignoring the blanks and only taking the average of the populated cells.…
    Formulas and Functions253
    SuzanneH
  • Hannah Johnson
    First post 01/07/20,Last post 01/08/20
    Hi! I've got a project tracking sheet that has the following columns: Estimated Start Date Actual Start Date I need to calculate how long a project has been waiting to start. Some projects don't have an actual start date, though. So th…
    Formulas and Functions451
    Hannah Johnson
  • dchapman
    dchapman 69 views 11 comments Most recent by Paul Newcome
    First post 09/25/18,Last post 01/08/20
    I would like to create a SUMIF equation to only sum data (column F) based on a date range (column B).  It works in excel with the following equation for reference. =SUMIF($B$9:$B$2000, ">=9/1/2018", $F$9:$F$2000)-SUMIF($B$9:$…
    Formulas and Functions6911
    dchapman
  • hbaseler
    hbaseler 92 views 7 comments Most recent by Paul Newcome
    First post 02/16/18,Last post 01/08/20
    I've a field for ClientName. I would very much like for subtasks to automatically inherit the Parent Card ClientName value. How might I do this? My users prefer using the Card view. They feel that if they have to use the spreadsheet view, …
    Formulas and Functions927
    hbaseler
  • abuckle
    Accepted Answer abuckle 32 views 3 comments Most recent by Paul Newcome
    First post 01/07/20,Last post 01/08/20
    I've created a quick Form to help my HR team collect votes from staff on 4 key areas. I've used the "Dropdown (multi select)" column type, and the Form asks staff to select the 2 options they rank as most important from these lis…
    Formulas and Functions323
    abuckle
  • Dee Barnard
    COUNTIFSAnswered
    Answered Dee Barnard 26 views 1 comment Most recent by Paul Newcome
    First post 01/07/20,Last post 01/07/20
    I am trying to count number of lines from project master list that are in a certain region and when the Forecast $ is greater than the Actual $. Looking for projects that were under the Forecast $. Simple formula but will not work. Plea…
    Formulas and Functions261
    Dee Barnard