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

  • Daniel Gomez
    Answered Daniel Gomez 11 views 2 comments Most recent by Daniel Gomez
    First post 01/22/20,Last post 01/23/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 Functions112
    Daniel Gomez
  • Edgar Alvarado García
    Question Edgar Alvarado García 7 views 0 comments Started by Edgar Alvarado García
    First post 01/23/20,Last post 01/23/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 Functions70
    Edgar Alvarado García
  • Jona Gjylameti
    Answered Jona Gjylameti 10 views 3 comments Most recent by @Diana
    First post 01/23/20,Last post 01/23/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 Functions103
    Jona Gjylameti
  • taylor.thompson89836
    Accepted Answer taylor.thompson89836 13 views 2 comments Most recent by taylor.thompson89836
    First post 01/23/20,Last post 01/23/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 Functions132
    taylor.thompson89836
  • @Diana
    Question @Diana 3 views 0 comments Started by @Diana
    First post 01/23/20,Last post 01/23/20
    Help! I cannot figure out a formula for SUMIF or SUMIFS to add a range of numbers in a column that are greater than 25,000 based on a checkbox. =SUMIFS({Content Limit - NB}, >25000, {Reporting - NB}, 1) <-- why doesn't this work. heh…
    Formulas and Functions30
    @Diana
  • Tina
    Combining formulasAccepted Answer
    Accepted Answer Tina 10 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 Functions102
    Tina
  • Guy Behanna
    Question Guy Behanna 4 views 0 comments Started by Guy Behanna
    First post 01/23/20,Last post 01/23/20
    I am certain this has been asked either currently or in the past, but is the capability to include a formula in the % Allocation column on the product roadmap? Right now, I have a column entitled "% Alloc Calc" next to the "…
    Formulas and Functions40
    Guy Behanna
  • Angela Ryer
    Answered Angela Ryer 28 views 5 comments Most recent by Angela Ryer
    First post 01/17/20,Last post 01/23/20
    =IF(Status6 = "Open", IF([Due Date]6 <= TODAY(2), 1, 0), (IF([Send to High Risk Report Now]6 = 1, 1, 0))) https://us.v-cdn.net/6031209/uploads/777/529C59A6UPMV.png I have this formula that each IF function works on its own, b…
    Formulas and Functions285
    Angela Ryer
  • mel.greenspan
    Question mel.greenspan 7 views 0 comments Started by mel.greenspan
    First post 01/23/20,Last post 01/23/20
    We want to use icons that relate to our work. I have an IF formula that includes emojis. =IF(Classification4 = "BAGS", "🎒", IF(Classification4 = "SOCKS", "🧦", IF(Classification4 = "GLOVES"…
    Formulas and Functions70
    mel.greenspan
  • JoeS
    JoeS 241 views 16 comments Most recent by Tommy Ray
    First post 08/28/19,Last post 01/23/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 Functions24116
    JoeS
  • M. David
    Accepted Answer M. David 28 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 Functions2811
    M. David
  • Sean Taber
    Answered Sean Taber 153 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 Functions15318
    Sean Taber
  • Nikhil Chawla
    Answered Nikhil Chawla 14 views 1 comment Most recent by [email protected]
    First post 01/23/20,Last post 01/23/20
    Hello, I am using the below formula to count the number of rows where 140R was checked previous month. This formula was working perfectly fine until this month when the year changed. How can I fix this formula where previous month should b…
    Formulas and Functions141
    Nikhil Chawla
  • lhumphrey
    Accepted Answer lhumphrey 9 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 Functions93
    lhumphrey
  • Dedrick Tai
    Answered Dedrick Tai 16 views 1 comment Most recent by Genevieve P.
    First post 01/22/20,Last post 01/23/20
    Hello, Looking for formula help. I have a master Smartsheet with email addresses and a second Smartsheet with a form that will populate with email addresses entered by the user, so they may not be entered 100% correctly (fuzzy lookup?). I…
    Formulas and Functions161
    Dedrick Tai
  • Heidi Ng
    Question Heidi Ng 6 views 0 comments Started by Heidi Ng
    First post 01/23/20,Last post 01/23/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 Functions60
    Heidi Ng
  • ferguson_pamela107206
    Answered ferguson_pamela107206 22 views 5 comments Most recent by Paul Newcome
    First post 01/22/20,Last post 01/23/20
    So I need some help with my design or nested formulas..... I have a formula in a column (listed below) which is perfect, except that in a few instances there are exceptions, and I must add up several different $ amounts from a few CLINS on…
    Formulas and Functions225
    ferguson_pamela107206
  • fennerb
    Answered fennerb 19 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 Functions195
    fennerb
  • Sitlaly Avelino
    Accepted Answer Sitlaly Avelino 13 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 Functions133
    Sitlaly Avelino
  • PRITIKA SAINI
    Accepted Answer PRITIKA SAINI 8 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 Functions82
    PRITIKA SAINI
  • Brigette Lepe
    Answered Brigette Lepe 19 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 Functions195
    Brigette Lepe
  • Scott Anderson
    Accepted Answer Scott Anderson 20 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 Functions203
    Scott Anderson
  • lcymr13
    Accepted Answer lcymr13 21 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 Functions213
    lcymr13
  • Beth Faircloth
    Question Beth Faircloth 4 views 0 comments Started by Beth Faircloth
    First post 01/23/20,Last post 01/23/20
    All, (Please see screenshot for reference) I am trying to get a "total locations" excluding if "cancelled" is checked. Current formula in "total locations is. =COUNT([Location #]:[Location #]) This gives the total…
    Formulas and Functions40
    Beth Faircloth
  • Laurence Bergeron
    Answered Laurence Bergeron 16 views 1 comment Most recent by Genevieve P.
    First post 01/22/20,Last post 01/23/20
    for exemple https://us.v-cdn.net/6031209/uploads/220/GBHD6PIX1YDQ.png In my sheet https://us.v-cdn.net/6031209/uploads/059/3CGRABEL8NHU.png i would like in my reponse here https://us.v-cdn.net/6031209/uploads/934/GFSNDQEV6AU6.png corre…
    Formulas and Functions161
    Laurence Bergeron
  • Lisa Kastelic
    Accepted Answer Lisa Kastelic 15 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 Functions155
    Lisa Kastelic
  • System
    Answered System 14 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 Functions144
    System
  • Laurent DESSOLLES
    Laurent DESSOLLES 23 views 7 comments Most recent by Andrée Starå
    First post 01/17/20,Last post 01/23/20
    Hello, I actually have some #NOMATCH problem, that I can't explain because it work different cells of my sheet that have same parameters. My formula is : =IF(ISBOOLEAN(VLOOKUP([N° CONTAINER]1; {LDS Container Dashboard plage 2}; 21)) = 1; 1…
    Formulas and Functions237
    Laurent DESSOLLES
  • Krishna Patel
    Question Krishna Patel 12 views 0 comments Started by Krishna Patel
    First post 01/23/20,Last post 01/23/20
    Hello, I have 2019 monthly data in Sheet 1 and would like for it to be populated in Sheet 2 based on current 2020 month. Basically, I want to pull same month last year data from sheet 1 to sheet 2. I've tried using VLOOKUP in sheet 2 cell …
    Formulas and Functions120
    Krishna Patel
  • davidmartin95826
    Accepted Answer davidmartin95826 12 views 6 comments Most recent by Paul Newcome
    First post 01/23/20,Last post 01/23/20
    Hello, I have an Procurement Engagement Form (PEF) and a Resource Tracker. I'm trying to pull the Contract End Date into the Resource Tracker using the Function below. Its working perfectly in Excel, although unfortunately I'm struggling…
    Formulas and Functions126
    davidmartin95826