Formulas and Functions

Formulas and Functions

Discussion List

  • Shannon Petersen
    Answered - Pending Review Shannon Petersen 40 views 6 comments Most recent by Andrée Starå
    First post 06/24/20,Last post 07/29/20
    I'm trying to count the number of instances of a keyword in one cell (for instance if I have "Green Apples; Yellow Bananas; Brown Bananas; Reg Apples" in my Products column for this row, I want to count the number of times "…
    Formulas and Functions406
    Shannon Petersen
  • Susan Grunenwald
    Answered - Pending Review Susan Grunenwald 24 views 4 comments Most recent by Andrée Starå
    First post 05/26/20,Last post 07/29/20
    I created a work flow to copy a row to another sheet. At first i use criteria when the reference number changes to any value, copy to another sheet. that worked. But when i added additional criteria of "AND when the Date of Final Appr…
    Formulas and Functions244
    Susan Grunenwald
  • Ruth Potts
    CountIFsQuestion
    Answered - Pending Review Ruth Potts 13 views 5 comments Most recent by Andrée Starå
    First post 07/28/20,Last post 07/29/20
    Hi I am trying to do a count if to see the following if the value selected is Milestone and the status selected is Complete at the moment I have tried this =COUNTIFS(Value:Value, "Milestone", Status:Status, "Complete"…
    Formulas and Functions135
    Ruth Potts
  • Marilen.Navarro103391
    WEEKLY COUNTAccepted Answer
    Accepted Answer Marilen.Navarro103391 24 views 7 comments Most recent by Paul Newcome
    First post 07/28/20,Last post 07/29/20
    Hello, I need to create a report showing weekly "BOOKED" per category and associate. Weekly is from M-F. (Image) My sheet looks like this below: (Image) Thank you so much.
    Formulas and Functions247
    Marilen.Navarro103391
  • Natasha D
    Answered - Pending Review Natasha D 15 views 4 comments Most recent by Paul Newcome
    First post 07/28/20,Last post 07/29/20
    Having a blank mental moment. I am trying to create a sum if formula to total all of the "medium" priorities using a range of numbers. High is fine as i just did a greater or equal to (>=) and likewise (<=) for the low. Whe…
    Formulas and Functions154
    Natasha D
  • Josh Lowry
    Accepted Answer Josh Lowry 17 views 3 comments Most recent by Paul Newcome
    First post 07/28/20,Last post 07/29/20
    Hello, I'm trying to figure out the right formula to change the RYG status ball depending on the status set in two different cells. I've tried various IF and OR combinations referenced in community threads but am missing something. Here's …
    Formulas and Functions173
    Josh Lowry
  • John Kelly
    Answered - Pending Review John Kelly 8 views 1 comment Most recent by Andrée Starå
    First post 07/29/20,Last post 07/29/20
    Hi, I'm looking to track information that has been received late on some sheets. See below example where date information required was 17th July and we received the info on the 26th July. I am using =IF([Date Information Received]@row >…
    Formulas and Functions81
    John Kelly
  • Terry Bonet
    Answered - Pending Review Terry Bonet 23 views 2 comments Most recent by Andrée Starå
    First post 07/21/20,Last post 07/29/20
    I want my column for progress (Not started - In progress - Complete) to update when someone adds the % complete. So is "0" then read "Not Started", If < the 0 but not 100% then "In Progress" and if 100% the…
    Formulas and Functions232
    Terry Bonet
  • John Crabtree
    Answered - Pending Review John Crabtree 12 views 1 comment Most recent by Andrée Starå
    First post 07/28/20,Last post 07/29/20
    In an effort to scale up a department Smartsheet Dashboard I developed using Summary Sheet Reports for my organization, I am faced with trying to include data from a category where the category information is unknown. Meaning the category …
    Formulas and Functions121
    John Crabtree
  • LLaCosta
    LLaCosta 34 views 3 comments Most recent by Genevieve P
    First post 11/15/19,Last post 07/29/20
    See attached image The Parent Row shows 7% complete based on the 1 item being 100% and all other items being in the future. The target % complete shows 20% which makes no sense to me.  There is a formula for Target % complete: =IFERROR(…
    Formulas and Functions343
    LLaCosta
  • majdesber
    majdesber 185 views 6 comments Most recent by Genevieve P
    First post 05/18/19,Last post 07/29/20
    Hello, Is there any formula to post(return) the name of the logged in user? I am trying to record the user name of the person who checked a box. for example, If I want to post a date when a box was checked, I write: =IF(Closed1 = 1, TODA…
    Formulas and Functions1856
    majdesber
  • Susan B Peck
    Answered - Pending Review Susan B Peck 18 views 1 comment Most recent by Andrée Starå
    First post 07/28/20,Last post 07/28/20
    Hi all, We have several task schedules that people use to track the status of their work. When the task is finished, they change the Status to Complete and the Percent Complete to 100%. We would also like to know the actual date the work…
    Formulas and Functions181
    Susan B Peck
  • Tammy Parker
    Answered - Pending Review Tammy Parker 12 views 1 comment Most recent by Andrée Starå
    First post 07/28/20,Last post 07/28/20
    Hello I have over 100 smartsheets with the exact same columns and questions. These sheets have been sent out to leadership teams across the country. I need to combine all this data into ONE summary sheet. Is there a way to do that? I do n…
    Formulas and Functions121
    Tammy Parker
  • Kacie Dean
    Answered - Pending Review Kacie Dean 11 views 1 comment Most recent by Paul Newcome
    First post 07/28/20,Last post 07/28/20
    Hi, I have a sheet with active jobs, some are scheduled and some are not. I want the Sheet Summary field to show the furthest date listed, but some of the rows do not have a date in the selected date column, and I want to be able to select…
    Formulas and Functions111
    Kacie Dean
  • beth_linder28141
    Answered - Pending Review beth_linder28141 91 views 51 comments Most recent by Paul Newcome
    First post 04/02/20,Last post 07/28/20
    I am trying to think my way through this and can't quite manage it yet. I have a log that has stages but the criteria is based upon the substrate chosen. (Image) If Hot dip is selected, the temperature spec is 120-160. If Cold Rolled St…
    Formulas and Functions9151
    beth_linder28141
  • JulieH
    Accepted Answer JulieH 17 views 2 comments Most recent by JulieH
    First post 07/28/20,Last post 07/28/20
    I'm having trouble with the multi select dropdown option. I have a sheet with the data/responses. I'm creating another reference sheet trying to count the responses. So example, here's my test data. There are multiple options for fruit. (I…
    Formulas and Functions172
    JulieH
  • Pamb10
    Accepted Answer Pamb10 29 views 5 comments Most recent by Guangda Shi
    First post 01/16/20,Last post 07/28/20
    I have a need to count rows where: Issue Type = Story Labels include LND-Product Status = Done Year of Resolution Date = 2020 COUNTIFS({JIRA Tickets Issue Type}, "Story", {JIRA Tickets Labels}, "LND-Product", {JIRA Tic…
    Formulas and Functions295
    Pamb10
  • Esperanza Zagal
    Answered - Pending Review Esperanza Zagal 20 views 12 comments Most recent by Paul Newcome
    First post 07/23/20,Last post 07/28/20
    (Image) Hi, I am trying to track progress through various stages of publication (the columns titled approval, data collection, analysis...accepted). I thought the health bubbles may be a good way to quickly visualize progress but it is c…
    Formulas and Functions2012
    Esperanza Zagal
  • Dominick Vargas
    Accepted Answer Dominick Vargas 12 views 5 comments Most recent by Paul Newcome
    First post 07/28/20,Last post 07/28/20
    Good morning, I have specific coding for each line item that reads as 7 digits with a "." followed by another 7 digits. So in essence, it looks like a long decimal. Smartsheets thinks it's a decimal and rounds it up, eliminating…
    Formulas and Functions125
    Dominick Vargas
  • Lori O'Daniel
    Accepted Answer Lori O'Daniel 11 views 3 comments Most recent by Paul Newcome
    First post 07/27/20,Last post 07/28/20
    I am attempting to take info from a column from two different sheets and combine the total. The formula I am working with to perform this for a single column is: =COUNTIF({Project Plan - Project A Range 1},="Complete") I tried us…
    Formulas and Functions113
    Lori O'Daniel
  • Wes
    Answered - Pending Review Wes 14 views 3 comments Most recent by Paul Newcome
    First post 07/25/20,Last post 07/28/20
    Where [Begin]@row = DATE(2020, 7, 1) and [End]@row = DATE(2020, 7, 31) The following formula returns returns incorrect value: =SUMIFS({Maint-MLR-LaborHours}, {Maint-MLR-DateCompleted}, AND(@cell >= [Begin]@row, @cell <= [End]@row)) B…
    Formulas and Functions143
    Wes
  • Rashaun Geter
    Accepted Answer Rashaun Geter 16 views 7 comments Most recent by Paul Newcome
    First post 07/27/20,Last post 07/28/20
    Hello, Basically, I am trying to write a formula in the Sheet Summary of a sheet that shows the contents of another cell on a different sheet. I am able to write a within sheet cell reference for example, =[Latest Update]77. However, I wa…
    Formulas and Functions167
    Rashaun Geter
  • Matt K
    Answered - Pending Review Matt K 31 views 8 comments Most recent by Paul Newcome
    First post 07/27/20,Last post 07/28/20
    Hi, I have a form that once completed populates a sheet. The form has about 100 items. The idea is that 100 items represent 5 categories. For each individual that completes the form I need to provide an average from the 100 responses that …
    Formulas and Functions318
    Matt K
  • Paul Molloy
    Summary SheetQuestion
    Answered - Pending Review Paul Molloy 14 views 3 comments Most recent by Paul Newcome
    First post 07/27/20,Last post 07/28/20
    Hi when i run a summary sheet, if the sheet uses drop down cells it does not pickup all the items I want to count. for example if i have Internet Access and TV Systems in same cell it does not count the Internet access when i try to do a…
    Formulas and Functions143
    Paul Molloy
  • Heiko E.
    Answered - Pending Review Heiko E. 14 views 7 comments Most recent by Paul Newcome
    First post 07/27/20,Last post 07/28/20
    Hi all, I'm searching for a solution on following idea: I have a sheet with following four columns: Created (date) | Category (Dropdown) | Previous Status (RYG balls) | Current Status (RYG balls) Now I want to auto-populate "Previous …
    Formulas and Functions147
    Heiko E.
  • Patrick Miller
    Accepted Answer Patrick Miller 19 views 6 comments Most recent by Paul Newcome
    First post 07/27/20,Last post 07/28/20
    Hi there, I am new to SmartSheets and am struggling to use the CONTAINS formula. I need to allow for the option for [Trial Type] to be either Sales Strip, Sales Demo, or both Sales Strip and Sales Demo The formula below doesn't work if bo…
    Formulas and Functions196
    Patrick Miller
  • Ashley Pierce
    Answered - Pending Review Ashley Pierce 24 views 2 comments Most recent by Lisa-B
    First post 07/09/20,Last post 07/28/20
    (Image) How can I get the Completion Rate to only show 100% rather than 150%?
    Formulas and Functions242
    Ashley Pierce
  • John_CGS
    Answered - Pending Review John_CGS 6 views 4 comments Most recent by Andrée Starå
    First post 07/28/20,Last post 07/28/20
    Morning Smartsheeters.. Was hoping someone could shed some light on why this statement isn't working.. =IF(Part75 = "Labour", [Supply Price]75 * [Quantity]75) I'm trying to multiply two cells based on whether the labour cost is…
    Formulas and Functions64
    John_CGS
  • bbelman
    bbelman 14 views 2 comments Most recent by Andrée Starå
    First post 07/24/20,Last post 07/28/20
    Hey - I exported my smartsheet to excel (to add a large number of rows) and then uploaded it. I know that column properties reverted to text/number.. and I've changed them back as needed.. except.. the Predecessor column.. can't get that …
    Formulas and Functions142
    bbelman
  • SJO
    Accepted Answer SJO 38 views 9 comments Most recent by Andrée Starå
    First post 07/17/20,Last post 07/28/20
    I am index/ matching from another sheet and it says no match, but there is clearly a match! I have moved the information into the same sheet to see if it would pick it up and it does but only some rows. I don't get it!
    Formulas and Functions389
    SJO