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

  • CamCrichton
    CamCrichton 88 views 8 comments Most recent by Paul Newcome
    First post 12/20/18,Last post 01/23/20
    I have a sheet with a list of assets. There is a pull down menu that defines the status - I need to know when the menu is set to "complete". I can do that easily with a filter - but I want to cull the entries before a specific da…
    Formulas and Functions888
    CamCrichton
  • Robert Stuffings
    Accepted Answer Robert Stuffings 29 views 6 comments Most recent by Paul Newcome
    First post 01/22/20,Last post 01/23/20
    =COUNTIFS({Quality Inspection System (Failures) Range 2}, "01-05204", {Date}, >=TODAY(-90), {Date}, <TODAY()) I am new to writing scripts and am kind of stumbling about. I have used the TODAY function once on another sheet …
    Formulas and Functions296
    Robert Stuffings
  • Nasir@EBC
    Counting Unique EmployeesAccepted Answer
    Accepted Answer [email protected] 30 views 5 comments Most recent by Paul Newcome
    First post 01/21/20,Last post 01/23/20
    Hi All I collect data from the staff in the following form: || Date || Employee Name || Task || Units Done || Time Taken || I want to write a formula that answers the following question: How many unique employees worked on "Tas…
    Formulas and Functions305
    [email protected]
  • Kelly Buse
    Answered Kelly Buse 40 views 11 comments Most recent by Paul Newcome
    First post 12/18/19,Last post 01/23/20
    Hello, Another request for information! I have a parent status ball that I would like to change color based on the 2 deadline status balls and the dominant completion ball (i.e. row 1 & row 2 - then row 3 being the dominant ball). The…
    Formulas and Functions4011
    Kelly Buse
  • mmac
    Answered mmac 17 views 5 comments Most recent by Paul Newcome
    First post 01/22/20,Last post 01/23/20
    Hello, I'm looking to accomplish the following and am hopeful that someone will be able to help support. Thank you in advance. I have a Live, Start Date and End Date column. What I am proposing to accomplish: Live = Red, if the Start Date/…
    Formulas and Functions175
    mmac
  • Rob Kozak
    Rob Kozak 31 views 9 comments Most recent by Rob Kozak
    First post 01/17/20,Last post 01/23/20
    Hi team, I'm using Placeholders in a workflow to generate individual emails for an update request. Although the same individual received the update, they will forward as needed, so I need to ensure each update request for the day doesn't …
    Formulas and Functions319
    Rob Kozak
  • Jaye Tatone
    Question Jaye Tatone 25 views 0 comments Started by Jaye Tatone
    First post 01/22/20,Last post 01/23/20
    Wondering if anybody else is experiencing this - I have performed this scenario hundreds of times without issue so wondering what's up! I have a formula based on full Column:Column ranges. The criteria ranges are set with $, the range to s…
    Formulas and Functions250
    Jaye Tatone
  • Terry Ables
    First post 01/21/20,Last post 01/23/20
    I'm trying have have my rows automatically update priority while allowing the user to move the rows to arrange their desired priority order. Its all working good with the exception if i move the first row or try to move another row as the…
    Formulas and Functions260
    Terry Ables
  • Chuen
    Answered Chuen 8 views 1 comment Most recent by Andrée Starå
    First post 01/23/20,Last post 01/23/20
    The column name is [Task] and a cell value called "Project Details" is located somewhere in the column. Their children is project 1, project2, ... etc.... I want to count the number of children under "Project Details". …
    Formulas and Functions81
    Chuen
  • sahilhq
    Accepted Answer sahilhq 6 views 1 comment Most recent by Andrée Starå
    First post 01/23/20,Last post 01/23/20
    Hi, I have created a chart widget in Smartsheet dashboard and I would like the horizontal axis to go up to the value of 100 only and not 120. Is there any way to bring it to 100? Please see attached images of the chart and the data that t…
    Formulas and Functions61
    sahilhq
  • mmac
    Answered mmac 22 views 1 comment Most recent by Genevieve P.
    First post 01/22/20,Last post 01/23/20
    Hello, I'm looking to accomplish the following and am hopeful that someone will be able to help support. Thank you in advance. Working with the following columns: Start Date End Date Sale Status Assignee QA Status Scenarios: If a sale…
    Formulas and Functions221
    mmac
  • Rebecca Panaccione
    Answered Rebecca Panaccione 27 views 4 comments Most recent by Genevieve P.
    First post 01/17/20,Last post 01/23/20
    Hello, I could use assistance building a JOIN COLLECT formula, I think. Here is what I'm trying to do. Using the formula =JOIN(COLLECT(rangecriterion_range1criterion1[criterion_range2​criterion2​...]) , I don't understand what I'm colle…
    Formulas and Functions274
    Rebecca Panaccione
  • Sara@Peer Seattle
    Accepted Answer [email protected] Seattle 13 views 3 comments Most recent by Andrée Starå
    First post 01/22/20,Last post 01/23/20
    I'm trying to figure out how to set up an alert to automatically notify someone if the entry row hasn't met criteria (checkbox) within 5 days. I was thinking about creating a flag column that kicks off an email alert but I can't figure out…
    Formulas and Functions133
    [email protected] Seattle
  • Mark Weighner
    First post 01/16/20,Last post 01/23/20
    When creating a work flow, how to I get the alert to trigger based on a if a value "greater than" a certain amount of time?
    Formulas and Functions193
    Mark Weighner
  • Justin Runner
    Accepted Answer Justin Runner 38 views 7 comments Most recent by Andrée Starå
    First post 01/21/20,Last post 01/23/20
    I'm looking for a formula to help me calculate the length of hours an individual has worked. Employees enter their "Haul Start Time" and "Haul End Time" manually by picking from a drop down list of times in 24hr format.…
    Formulas and Functions387
    Justin Runner
  • Stewart Ennew
    Accepted Answer Stewart Ennew 11 views 2 comments Most recent by Stewart Ennew
    First post 01/22/20,Last post 01/23/20
    Hi i'm trying to count the number of rows in a Smartsheet where the name in the "Engineer" column is the same name in the "Approved By" column I can get it to work by looking at a single row =IF(Engineer4 = [Approved By…
    Formulas and Functions112
    Stewart Ennew
  • Jana@al-a
    [email protected] 299 views 16 comments Most recent by Andrée Starå
    First post 05/17/19,Last post 01/23/20
    Hey Is it possible to have the the drop-down list of a column changed based on the value of another column? Eg. If column A's value is set to "Work stage 2" column B's drop-down list will have a specific list, but when it is se…
    Formulas and Functions29916
    [email protected]
  • Jerome Szurubura
    Accepted Answer Jerome Szurubura 15 views 4 comments Most recent by Julian Sindall
    First post 01/22/20,Last post 01/23/20
    Is it possible to link dropdown list choice to a specific column in a specific sheet? That would be really helpful to update automatically the dropdown list. Thanks for your support
    Formulas and Functions154
    Jerome Szurubura
  • @SPark
    SUM totals of VLOOKUPAccepted Answer
    Accepted Answer @SPark 19 views 3 comments Most recent by Paul Newcome
    First post 01/22/20,Last post 01/22/20
    Looking for help on a formula. I need the Forecast Adjustment column on (A) to add all amounts pulling (B). These should total $13,416.67, however, it it showing as $5,416.67. Please help! How do I change the formula to sum the VLOOKUPS? …
    Formulas and Functions193
    @SPark
  • Jenna Corso
    Accepted Answer Jenna Corso 25 views 3 comments Most recent by Paul Newcome
    First post 01/22/20,Last post 01/22/20
    Hello, I am trying to write a formula to return today's date when all of the following are met: Column 1 is not blank Column 2 is not blank Column 3 is either Kangaroo or Platypus If any of these is untrue, then I would like the cell to…
    Formulas and Functions253
    Jenna Corso
  • raymond.j.riosiii94806
    Answered raymond.j.riosiii94806 19 views 1 comment Most recent by Paul Newcome
    First post 01/22/20,Last post 01/22/20
    Hi Community, I need your help... I'm trying to create a formula that captures "No Action Taken". The individual IF Statements are as follows: If [Flu Vaccination Date] is "Blank" OR If [Flu Vaccination Date] is "…
    Formulas and Functions191
    raymond.j.riosiii94806
  • Sam Gold
    Sam Gold 17 views 4 comments Most recent by Paul Newcome
    First post 09/18/19,Last post 01/22/20
    I’m working on a SUMIFS formula to add/sum numbers in 2 or more separate cells based on a checkbox input in 2 or more other cells. I’m able to do so only if the number columns and the checkbox columns are next to another. Is there a way …
    Formulas and Functions174
    Sam Gold
  • Jerome Szurubura
    Answered Jerome Szurubura 10 views 1 comment Most recent by Paul Newcome
    First post 01/22/20,Last post 01/22/20
    Hi, I would like to know if there is a way to launch the smartsheet refreshment. I explain: I made many sheets with many cells linked together with formulas like Vlook up. When I change the searching criteria (for example a name include in…
    Formulas and Functions101
    Jerome Szurubura
  • prathap.krishnashetty69881
    Answered prathap.krishnashetty69881 45 views 2 comments Most recent by Genevieve P.
    First post 01/19/20,Last post 01/22/20
    https://us.v-cdn.net/6031209/uploads/800/9LTGMPMXFSEF.jpg https://us.v-cdn.net/6031209/uploads/037/6UC2BVUD0ABT.jpg Hi, i'm trying to filter values without YES in Column2 of sheet Y by Vlookup from Column2, Column3 of sheet X where Column3…
    Formulas and Functions452
    prathap.krishnashetty69881
  • Brian Lynch
    Brian Lynch 33 views 7 comments Most recent by Genevieve P.
    First post 11/01/19,Last post 01/22/20
    I'm new to this, and I'm having issues with counting the number of projects where Project Type = X and Project Status does Not equal Complete or Cancelled. I'm fumbling my way through unsuccessfully. This will go on a dashboard where I hav…
    Formulas and Functions337
    Brian Lynch
  • Shawn Borror
    Answered Shawn Borror 27 views 2 comments Most recent by Genevieve P.
    First post 01/20/20,Last post 01/22/20
    I can't figure out how to do this formula and I've been searching for it in community. I've tried the following: =COUNTIFS(Portfolio Rollups Range 1, "Corp IT",AND({Portfolio Rollups Range 8},="In Progress",{Portfolio …
    Formulas and Functions272
    Shawn Borror
  • Michael Palko
    Michael Palko 10 views 0 comments Started by Michael Palko
    First post 01/22/20,Last post 01/22/20
    I've built a Smartsheet (with some help from the ProDesk) that assigns a task to a contact based on the date/time of a request. The sheet looks at the date stamp on the request then references another sheet (using WEEKNUMBER and WEEKDAY) …
    Formulas and Functions100
    Michael Palko
  • AbbyT
    Answered AbbyT 25 views 3 comments Most recent by AbbyT
    First post 01/20/20,Last post 01/22/20
    I have my cashflow forecast on Smartsheet, and i have a formula which looks at the balance on the row above to apply the balance for the current row as follows =BALANCE1350 + [in's]1351 - [out's]@row. When i shift the row up or down within…
    Formulas and Functions253
    AbbyT
  • sahilhq
    Accepted Answer sahilhq 47 views 20 comments Most recent by sahilhq
    First post 01/05/20,Last post 01/22/20
    Hi, I need assistance in creating a calendar which will show the number of locations where material deliveries will happen (5 locations) over 24 hours - 365 days a year. The idea is to then create a dashboard with the form and calendar an…
    Formulas and Functions4720
    sahilhq
  • Andrea Katz
    Answered Andrea Katz 34 views 5 comments Most recent by Paul Newcome
    First post 01/07/20,Last post 01/22/20
    I'm wondering what the best way to do the following would be: I have about 100 districts and each one is assigned to one of four people. I want to populate a column with the names of the people associated with each district. Would this be …
    Formulas and Functions345
    Andrea Katz