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

  • soeirorenan
    Answered soeirorenan 29 views 1 comment Most recent by Genevieve P.
    First post 01/20/20,Last post 01/22/20
    Hi, I need to compare the hours allocated to the hours actually worked, through timesheets. For this I am using a spreadsheet to aggregate all the data from timesheets in order to extract metrics of hours worked by time and in which proj…
    Formulas and Functions291
    soeirorenan
  • Joshua Lewis
    Answered Joshua Lewis 28 views 3 comments Most recent by Genevieve P.
    First post 01/15/20,Last post 01/22/20
    I have a Smartsheet that will have values inputted each week and I would like to calculate an historical rolling average based on the last four weeks. What formula would I use to calculate this rolling average? Example data (Assuming c…
    Formulas and Functions283
    Joshua Lewis
  • Darren Mullen
    Answered Darren Mullen 35 views 2 comments Most recent by Darren Mullen
    First post 01/17/20,Last post 01/22/20
    Hi, I have a sheet with project workflows. Each workflow has an ID# and they have indented rows. I have a form that enters new rows into the top of the sheet. Everything is working fine as far as auto-filling all the gnarly formulas I…
    Formulas and Functions352
    Darren Mullen
  • Stephane
    Answered Stephane 31 views 1 comment Most recent by Genevieve P.
    First post 01/20/20,Last post 01/22/20
    In Smartsheet, there is a function called Countif(CHILDREN([reference])). This is a great formula but what if I wanted the count of children based on more than 1 condition? I searched in the knowledge base, but couldn't find either a 'coun…
    Formulas and Functions311
    Stephane
  • Amit Dahan
    Answered Amit Dahan 6 views 1 comment Most recent by Andrée Starå
    First post 01/22/20,Last post 01/22/20
    Hi, In certain sheets, where many conditional formatting rules, workflows and functions are set, every time I open the sheet it colors all rows in red and it offers me the option to save my changes although nothing has changed in the sheet…
    Formulas and Functions61
    Amit Dahan
  • Dajah
    Dajah 395 views 16 comments Most recent by Bradley Casado
    First post 11/27/18,Last post 01/22/20
    I understand I can likely do this with a very, very long VLOOKUP  or IF/THEN formula in every cell, but I'm dealing with over a hundred items and that will be unwieldy.  Situation: I would like to have a form where someone selects one op…
    Formulas and Functions39516
    Dajah
  • cnikkih
    Answered cnikkih 25 views 3 comments Most recent by Genevieve P.
    First post 01/17/20,Last post 01/22/20
    I am new to Smartsheets and cannot figure out the formula for a particular SUMIF. Basically, I need the total by Vendor & Year. I have an incredibly long list of invoices that I've imported into Smartsheets, and want to be able to use …
    Formulas and Functions253
    cnikkih
  • Meaghan Dunn
    Answered Meaghan Dunn 13 views 1 comment Most recent by Andrée Starå
    First post 01/21/20,Last post 01/22/20
    I'm trying to convert a fairly simple formula from excel into one of sheets that has multiple equations in it and can't seem to find the one that works best. formula in excel would be =(B45*B46)+B45
    Formulas and Functions131
    Meaghan Dunn
  • Julio Martinez Coronado
    Answered Julio Martinez Coronado 12 views 1 comment Most recent by [email protected]
    First post 01/21/20,Last post 01/22/20
    Hi, I'm using a template and it already allow me to flag a column when At Risk. I would like to count how many items I have flagged, but i'm not sure how. I've used =SUMIF({Sheet - Request Tracker with Form Range 1}, "Red") =C…
    Formulas and Functions121
    Julio Martinez Coronado
  • Brittany Hajicek
    Answered Brittany Hajicek 21 views 7 comments Most recent by Paul Newcome
    First post 01/21/20,Last post 01/21/20
    What is the easiest formula to use to determine the time difference between two Military Times? I'm using the 4 digit military time and also I need the formula to calculate across midnight. I'd like the time difference to be in minutes (bu…
    Formulas and Functions217
    Brittany Hajicek
  • Stephanie Stewart
    Accepted Answer Stephanie Stewart 13 views 3 comments Most recent by Paul Newcome
    First post 01/21/20,Last post 01/21/20
    Good morning, To determine the status of a row, I have the following formula (in the Status) column: =IF(Complete1 = "Yes", "COMPLETED", IF(Complete1 = "Cancelled", "CANCELLED", (IF(TODAY() >= [Du…
    Formulas and Functions133
    Stephanie Stewart
  • Zachary Mitchell
    Answered Zachary Mitchell 19 views 3 comments Most recent by Paul Newcome
    First post 01/21/20,Last post 01/21/20
    I'm trying to create a formula that will display the different colored dots in the status bar. I had success at first but one part of the formula isn't working with the others. I need it to display BLANK if Patient is blank, Gray if patien…
    Formulas and Functions193
    Zachary Mitchell
  • Adam Fink
    Answered Adam Fink 14 views 4 comments Most recent by Adam Fink
    First post 01/20/20,Last post 01/21/20
    Hello, I am using the following formula to represent health color for individual rows: =IF([Start Date]40 > TODAY(), "Blue", IF(AND([% Complete]40 < 1, [End Date]40 < TODAY()), "Red", IF(COUNTIFS(CHILDREN(), &q…
    Formulas and Functions144
    Adam Fink
  • Juan Castro
    Answered Juan Castro 12 views 1 comment Most recent by Andrée Starå
    First post 01/21/20,Last post 01/21/20
    The only option I found is URL and it needs to starts with http. Any help will be appreciated.
    Formulas and Functions121
    Juan Castro
  • Spud Software
    Answered Spud Software 27 views 3 comments Most recent by Andrée Starå
    First post 01/20/20,Last post 01/21/20
    I'm seeing that the System Modified field is NOT updated after entering a new comment on a row. Is there a way to report on the Last Comment Date? I am trying to identify a way to create a report on my sheet that shows Last Update dates, a…
    Formulas and Functions273
    Spud Software
  • Melissa.Holland80446
    Answered Melissa.Holland80446 27 views 11 comments Most recent by Andrée Starå
    First post 01/16/20,Last post 01/21/20
    In our project tracking sheet we have a column for Project/Task Name. The project name is listed in the parent row and then any tasks are listed below as child rows. The projects are added to the sheet by the Director who also assigns a pr…
    Formulas and Functions2711
    Melissa.Holland80446
  • styless
    vLookup and AVGAccepted Answer
    Accepted Answer styless 37 views 13 comments Most recent by Paul Newcome
    First post 01/10/20,Last post 01/21/20
    Hello, I am trying to do a vlookup to count the average days to fill for each Org Level, by Exempt VS Non-Exempt. This is the source sheet: https://app.smartsheet.com/b/publish?EQBCT=2b4a116ae47c4d17a13c6fea0d751347 Sheet I wanted the Vloo…
    Formulas and Functions3713
    styless
  • Shane Cornwell
    Accepted Answer Shane Cornwell 24 views 3 comments Most recent by Paul Newcome
    First post 01/16/20,Last post 01/21/20
    Hello - I am trying to build metrics that show how many projects are created with specific quarters based off a Date completed column. Currently using this formula but it is not working: =COUNTIFS({Project List Range 1}, "Closed"…
    Formulas and Functions243
    Shane Cornwell
  • AmandaM
    First post 01/17/20,Last post 01/21/20
    I have formulas used to create target finish dates. What I would like is to have the target finish date show the date based on the formula when the Actual finish date column is blank. But when the actual finish date is populated I would l…
    Formulas and Functions1911
    AmandaM
  • Alejandra
    Closed Alejandra 1 view 0 comments Started by Alejandra
    First post 01/20/20,Last post 01/20/20
    This discussion has been moved.
    Formulas and Functions10
    Alejandra
  • xnickeddlemanx
    Answered xnickeddlemanx 23 views 5 comments Most recent by [email protected]
    First post 01/17/20,Last post 01/20/20
    Hi Community, I am working with an INDEX MATCH formula to return content from another sheet; however, for each unique value, there are 4 duplicates. For example, an associate completed 4 learning events all attached to the same cohort na…
    Formulas and Functions235
    xnickeddlemanx
  • Anthony Verhoeven
    Anthony Verhoeven 15 views 0 comments Started by Anthony Verhoeven
    First post 01/20/20,Last post 01/20/20
    Good afternoon, Would it be possible to create a product that allows users to do a live search on sheets and even possibly aggregate data from multiple sheets to produce given search results. I have already built this through a customized…
    Formulas and Functions150
    Anthony Verhoeven
  • Sldollman
    Accepted Answer Sldollman 19 views 8 comments Most recent by Andrée Starå
    First post 01/17/20,Last post 01/20/20
    Hello community! I am attempting to use an IF statement in a contact column to insert a vendor name depending on the company name in the associated row. =IF(Company1 = "Hospitals", "Vendor Name1", "Vendor Name2&quo…
    Formulas and Functions198
    Sldollman
  • Hammermill21
    Accepted Answer Hammermill21 21 views 7 comments Most recent by Andrée Starå
    First post 01/17/20,Last post 01/20/20
    Hello everyone! I want to set up a workflow where I move a row when it check off as archived. But that row is a child of a parent row. How can I move both the child and parent row from my sheet, but also not actually remove the parent row …
    Formulas and Functions217
    Hammermill21
  • Mark Strecker
    Answered Mark Strecker 19 views 2 comments Most recent by Mark Strecker
    First post 01/17/20,Last post 01/20/20
    Hello, I have two columns, one has job numbers in it, and the one beside it has this formula to detect if the first coiumn has duplicates: =IF(COUNTIF([Complete Job Number]:[Complete Job Number], [Complete Job Number]51) > 1, 1). The ro…
    Formulas and Functions192
    Mark Strecker
  • Mitch@Enmach
    Accepted Answer [email protected] 29 views 5 comments Most recent by Andrée Starå
    First post 01/16/20,Last post 01/20/20
    Hello Smartsheet Community, Would anybody know how to write a formula to tick a check box once a date in another column is reached. Many thanks in advance😊
    Formulas and Functions295
    [email protected]
  • Jeanice Truong
    Accepted Answer Jeanice Truong 20 views 1 comment Most recent by Genevieve P.
    First post 01/17/20,Last post 01/20/20
    Hi I am trying to find the average time to complete a task without adding additional columns in my sheet. I would like to set it up in the sheet summary for easy reporting. In excel I would use the following formula. If a cell is blank th…
    Formulas and Functions201
    Jeanice Truong
  • Lindsey P
    Lindsey P 57 views 8 comments Most recent by AmandaM
    First post 03/18/19,Last post 01/20/20
    Hello I have a parent row on a sheet and I would like new entries that flow into that sheet through a form to be automatically indented so it's a part as a child row to the parent row from the beginning. Also, there is a formula that isn'…
    Formulas and Functions578
    Lindsey P
  • jb@59069
    [email protected] 14 views 1 comment Most recent by Genevieve P.
    First post 01/17/20,Last post 01/20/20
    I am logged into the Community, looking for help on formula using IF(OR. No matter what I try searching for, I get: "No results found." I can search for "if", "No results found." It doesn't matter what I searc…
    Formulas and Functions141
    [email protected]
  • Cheryl Crouse
    First post 01/19/20,Last post 01/19/20
    I have a sheet where we do calculations such as Tasks coming due in the Next 7 days "=COUNTIFS({AF}, <=TODAY(+7), {PComp}, <>1)".  When we used the metric widget on the dashboard, it didn't update the numbers each day.  …
    Formulas and Functions121
    Cheryl Crouse