Formulas and Functions

Formulas and Functions

Let the community help you with that tricky formula or function! See how others are utilizing the power of formulas and functions to save them time and create more powerful solutions with Smartsheet.

Discussion List

  • Mary Smith
    Accepted Answer Mary Smith 24 views 4 comments Most recent by Andrée Starå
    First post 01/24/20,Last post 01/26/20
    Hi there! I have the following columns: Actual Invoice Date, Due Date, Invoice Status and, Days Past Due. First, I'd like to have the Invoice Status display SENT if there is a date present the Invoice Date column and then have it display P…
    Formulas and Functions244
    Mary Smith
  • Rob Kozak
    Rob Kozak 87 views 11 comments Most recent by Andrée Starå
    First post 01/17/20,Last post 01/26/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 Functions8711
    Rob Kozak
  • M. David
    Answered - Pending Review M. David 54 views 1 comment Most recent by Andrée Starå
    First post 01/24/20,Last post 01/26/20
    I would like to enter a formula into Sheet Summary that will show the last time a sheet was edited by a specific person. That person is identified on every row in an "Assigned To" column. I have both the Modified and Modified By …
    Formulas and Functions541
    M. David
  • Mark Weighner
    Answered - Pending Review Mark Weighner 14 views 1 comment Most recent by Andrée Starå
    First post 01/25/20,Last post 01/26/20
    How do I get a formula to copy down the colunm automatically?
    Formulas and Functions141
    Mark Weighner
  • Robert Stuffings
    Accepted Answer Robert Stuffings 41 views 7 comments Most recent by Andrée Starå
    First post 01/22/20,Last post 01/25/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 Functions417
    Robert Stuffings
  • Art Miller
    Accepted Answer Art Miller 21 views 2 comments Most recent by Andrée Starå
    First post 01/24/20,Last post 01/25/20
    Hello, Creating a dashboard with one source sheet and want to show the latest date when any row is modified in the sheet. Easy to select just one cell to represent this with the metric widget. However I need a formula that will pull th…
    Formulas and Functions212
    Art Miller
  • Joanie Velles
    Answered - Pending Review Joanie Velles 11 views 2 comments Most recent by Andrée Starå
    First post 01/24/20,Last post 01/25/20
    Hello, I am trying to run a SUMIF formula off of a different Smartsheet using the person in the contact lists formula. I've created the SUMIF Formula below, but it is not totaling the FTE column (Range 4). If someone could please advise, …
    Formulas and Functions112
    Joanie Velles
  • Angela Ryer
    Accepted Answer Angela Ryer 98 views 12 comments Most recent by Paul Newcome
    First post 01/17/20,Last post 01/24/20
    =IF(Status6 = "Open", IF([Due Date]6 <= TODAY(2), 1, 0), (IF([Send to High Risk Report Now]6 = 1, 1, 0))) (Image) I have this formula that each IF function works on its own, but i can't seem to get them to work together. Obje…
    Formulas and Functions9812
    Angela Ryer
  • Robyn Pier
    Answered - Pending Review Robyn Pier 11 views 4 comments Most recent by Robyn Pier
    First post 01/24/20,Last post 01/24/20
    I can't find a formula that works to do a tally of how many patients I had in each month. Would I do a COUNT and then somehow determine it by month? Help please??? :)
    Formulas and Functions114
    Robyn Pier
  • JPJ
    Accepted Answer JPJ 46 views 7 comments Most recent by JPJ
    First post 01/24/20,Last post 01/24/20
    Hi I have a countif formula =COUNTIF(Wed10:Wed51, "HPT" to count the number of times HPT shows in a column. I want to format the result by color compared to another cell. For example: i need 5 however; the countif returns 4(count…
    Formulas and Functions467
    JPJ
  • Stephane
    Accepted Answer Stephane 262 views 3 comments Most recent by Genevieve P
    First post 01/20/20,Last post 01/24/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 Functions2623
    Stephane
  • taylor.thompson89836
    Accepted Answer taylor.thompson89836 608 views 3 comments Most recent by [email protected]
    First post 01/23/20,Last post 01/24/20
    I am trying to find the average of time in position (demographics range 1)when the job class (demographics range 3) is manager or higher. I'm currently using =avg(collect to try to specify the multiple criteria but am getting a #divide by …
    Formulas and Functions6083
    taylor.thompson89836
  • Jona Gjylameti
    Accepted Answer Jona Gjylameti 43 views 6 comments Most recent by Paul Newcome
    First post 01/23/20,Last post 01/24/20
    Hello, I need a formula that can help me calculate the sum within a column based on date. Not sure if this can be achieved. We are updating information on a weekly basis and I have different rows for the same date. I need a formula to give…
    Formulas and Functions436
    Jona Gjylameti
  • Edgar Alvarado García
    Answered - Pending Review Edgar Alvarado García 23 views 3 comments Most recent by Paul Newcome
    First post 01/23/20,Last post 01/24/20
    Hi there, In a sheet I've few cells that have formulas, columns were those cells are aren't lock, however the users cannot overwrite the formulas, any idea why it may happen? The idea was to create formulas as a guidance but editors shoul…
    Formulas and Functions233
    Edgar Alvarado García
  • Heidi Ng
    Answered - Pending Review Heidi Ng 64 views 1 comment Most recent by Andrée Starå
    First post 01/23/20,Last post 01/24/20
    Hello, I have two separate column Status and Due Date and I'm looking to create a formula on the sheet summary for the amount of items that are due today and past due. Is there a way to create one?
    Formulas and Functions641
    Heidi Ng
  • JoeS
    JoeS 2.5K views 17 comments Most recent by Paul Newcome
    First post 08/28/19,Last post 01/24/20
    Sheet 1: Work Scheduled (number of work units scheduled in a given month) Sheet 2: Budgeted Cost of Work Scheduled (to determine the total cost of the work scheduled in a given month) I would like to multiply a cell from Sheet 2 with the…
    Formulas and Functions247017
    JoeS
  • Daniel Gomez
    Answered - Pending Review Daniel Gomez 105 views 3 comments Most recent by Andrée Starå
    First post 01/22/20,Last post 01/24/20
    Hello, I am trying to count the amount of times a particular "ticket category" is selected. Some of our tickets have multiple values to be applied and I am noticing that my formula does not count an item if there was more than …
    Formulas and Functions1053
    Daniel Gomez
  • Tina
    Combining formulasAccepted Answer
    Accepted Answer Tina 21 views 2 comments Most recent by Tina
    First post 01/23/20,Last post 01/23/20
    Hi, I need some help with a formula. I have a one check mark column that I would like to 'check' if any of the following formulas are true. Can I combine them into one large formula? =IF(AND([Key Activity]@row, 1, [CEC Tech]@row, 1), 1) …
    Formulas and Functions212
    Tina
  • M. David
    Accepted Answer M. David 43 views 11 comments Most recent by Paul Newcome
    First post 01/22/20,Last post 01/23/20
    Hello. I worked with someone on a previous discussion, but for some reason cannot locate it now. I have one data base file with the work orders from many different community locations. I wanted a solution for the Work Order Number to be a …
    Formulas and Functions4311
    M. David
  • Sean Taber
    Accepted Answer Sean Taber 1.3K views 18 comments Most recent by Genevieve P
    First post 06/18/18,Last post 01/23/20
    Hello, I'm trying to calculate the average number of days but only for columns that meet the criteria.  I have a formula that is calculating the networkdays between two dates. This column is called Implementation Time (Days) - BD. I am no…
    Formulas and Functions134918
    Sean Taber
  • lhumphrey
    Accepted Answer lhumphrey 25 views 3 comments Most recent by [email protected]
    First post 01/23/20,Last post 01/23/20
    I can't seem to beat this formula. I've tried COUNTIF, COLLECT, CONTAINS, and when I'm not getting an error I'm getting 0. I want to see the average of successful deliveries below for all Up to Date emails in Q1. In the sheet I'm working w…
    Formulas and Functions253
    lhumphrey
  • fennerb
    Answered - Pending Review fennerb 34 views 5 comments Most recent by Paul Newcome
    First post 01/22/20,Last post 01/23/20
    Hello, I'm trying to calculate average work days that each command takes to return a request. However, it has to meet several other stipulations. -"Type of Request" has to include "FOIA" (drop-down column multi-select) …
    Formulas and Functions345
    fennerb
  • Sitlaly Avelino
    Accepted Answer Sitlaly Avelino 21 views 3 comments Most recent by Paul Newcome
    First post 01/22/20,Last post 01/23/20
    Is there a way to allow a user without access to the smartsheet to select a time and date? We use the automation to send emails out to contractors so that they can give us a work start date. However, we also want to know the exact time of…
    Formulas and Functions213
    Sitlaly Avelino
  • PRITIKA SAINI
    Accepted Answer PRITIKA SAINI 37 views 2 comments Most recent by PRITIKA SAINI
    First post 01/23/20,Last post 01/23/20
    Hello I am currently using both currencies in my sheet. I created a column drop down, need help with a formula. If I select USD from that column, the amount should be converted to CAD if I select CAD from that drop down column then the am…
    Formulas and Functions372
    PRITIKA SAINI
  • Brigette Lepe
    Answered - Pending Review Brigette Lepe 22 views 5 comments Most recent by Andrée Starå
    First post 01/22/20,Last post 01/23/20
    I am trying to have some date fields auto-populate based on some restrictions. Date Columns 2, 3 and 4 would be dependent on Date column 1. For example: An Inspection must always occur at least 2 days after Work Start. If I enter Work Star…
    Formulas and Functions225
    Brigette Lepe
  • Scott Anderson
    Accepted Answer Scott Anderson 23 views 3 comments Most recent by Genevieve P
    First post 01/22/20,Last post 01/23/20
    I have assessment scores for individuals within different groups (Male or Female). The assessments are taken over time as a sort of "pulse check." Now, I want to be able show a group average (average for all Males and average for…
    Formulas and Functions233
    Scott Anderson
  • lcymr13
    Accepted Answer lcymr13 54 views 3 comments Most recent by Paul Newcome
    First post 01/22/20,Last post 01/23/20
    Oh formulas. . . I feel like I am close, but could use some help. I would like the 'risk' flag to be marked if the data meets the following criteria: % complete is equal to or less than 50% AND The date is within 3 days; Today; or is in t…
    Formulas and Functions543
    lcymr13
  • Laurence Bergeron
    Answered - Pending Review Laurence Bergeron 21 views 1 comment Most recent by Genevieve P
    First post 01/22/20,Last post 01/23/20
    for exemple (Image) In my sheet (Image) i would like in my reponse here (Image) corresponding with me cell client rfi # info (Image)
    Formulas and Functions211
    Laurence Bergeron
  • Lisa Kastelic
    Accepted Answer Lisa Kastelic 21 views 5 comments Most recent by Lisa Kastelic
    First post 01/23/20,Last post 01/23/20
    I am trying to count the number of tasks assigned to a resource, let's say "Jane". The COUNTIF function works if there is only one resource in the "Assigned To" category, but I also need to include the number of times …
    Formulas and Functions215
    Lisa Kastelic
  • System
    Answered - Pending Review System 44 views 4 comments Most recent by Rebecca Ciastko
    First post 01/22/20,Last post 01/23/20
    This discussion was created from comments split from: How is the checkbox function recognised?.
    Formulas and Functions444
    System