Formulas and Functions

Formulas and Functions

Discussion List

  • UnaB
    UnaB 274 views 11 comments Most recent by Paul Newcome
    First post 11/20/18,Last post 03/23/20
    I'm trying to see if formulas can reference reports or just root SS. I ran a report for all late tasks assigned to all people in a department. I then wanted to use a COUNTIF formula that would sum up all the late tasks of a certain person.…
    Formulas and Functions27411
    UnaB
  • JennS_
    Sumif from multiple sheetsAnswered Question
    Answered JennS_ 23 views 1 comment Most recent by Sean Morgan
    First post 03/19/20,Last post 03/23/20
    I need help with a formula for 2 scenarios: 1) I want to sum up the total of items from the "ATL Tasks/Notes" sheet that have any priority designation (high, medium or low). 2) Then I also want to sum up the total of items betw…
    Formulas and Functions231
    JennS_
  • Maria Chicas
    Answered Maria Chicas 20 views 1 comment Most recent by Genevieve P
    First post 03/20/20,Last post 03/23/20
    Dear all, I need to support to determine a formula that allows me To perform a weekly count of how many days (or if If any) will happen this week based on range of between 2 dates. Example Today is 3/19/2020 Start Date End Date 3/16…
    Formulas and Functions201
    Maria Chicas
  • Kylie English
    Answered Kylie English 17 views 1 comment Most recent by Paul Newcome
    First post 03/22/20,Last post 03/23/20
    Hi all, I have 4 cells, what I need is: Cell 1 to equal Cell 2 If Cell 2 is blank, then read Cell 3, If Cell 3 is blank, then read Cell 4. I have searched quite a few discussions to try and replicate but cannot seem to get it to work. Ce…
    Formulas and Functions171
    Kylie English
  • Ramsay Zaki
    Answered Ramsay Zaki 15 views 1 comment Most recent by Paul Newcome
    First post 03/23/20,Last post 03/23/20
    I would like to parse the text from a string in another cell. However, the string might not always be the same length so I cannot use LEFT or RIGHT. Here are two examples of what the string might look like: 1,5,7,33,15 13,22,8,1,16 Both ex…
    Formulas and Functions151
    Ramsay Zaki
  • An Nguyen
    Answered An Nguyen 23 views 11 comments Most recent by Paul Newcome
    First post 03/18/20,Last post 03/23/20
    Hi everyone, I am currently struggling to add more conditions to my current formula. The formula is: =IF('Department 1'[Approval Response]="Pending" && 'Department 1'[Past Due/Scheduled]="Past Due",1,0) I want …
    Formulas and Functions2311
    An Nguyen
  • Shaun Dickens
    Accepted Answer Shaun Dickens 19 views 3 comments Most recent by Paul Newcome
    First post 03/20/20,Last post 03/23/20
    Hi guys, I can't figure out what's happening here, hoping someone can point me in the right direction Importing a table with a "Date" column set as primary column, Date format is YYYY-MM-DD Trying to convert the value into a form…
    Formulas and Functions193
    Shaun Dickens
  • colin alexander
    Accepted Answer colin alexander 31 views 4 comments Most recent by Paul Newcome
    First post 03/19/20,Last post 03/23/20
    I have a calibration sheet that lists the number of months required between calibration dates and a column that states when the last calibration was carried out. I would like to use these 2 columns to calculate the date when the equipment …
    Formulas and Functions314
    colin alexander
  • Angela Ryer
    Accepted Answer Angela Ryer 28 views 5 comments Most recent by Paul Newcome
    First post 03/19/20,Last post 03/23/20
    ="Completed " + COUNTIF([Completed Tasks]:[Completed Tasks], 1) + " of " + COUNT([Completed Tasks]:[Completed Tasks], IF([Task Name]:[Task Name], "")) I have a checkbox column that will read in the summary ba…
    Formulas and Functions285
    Angela Ryer
  • Amy Arnold
    Answered Amy Arnold 14 views 1 comment Most recent by Genevieve P
    First post 03/19/20,Last post 03/23/20
    Hi, I'm trying to track if a site (or Name) created a new daily entry using a Smartsheet form. I added this formula to the Updated Today column to return a "Yes" answer if the site (or Name) created a new daily entry. This seems…
    Formulas and Functions141
    Amy Arnold
  • Mitch@Enmach
    Accepted Answer [email protected] 20 views 5 comments Most recent by Andrée Starå
    First post 02/26/20,Last post 03/23/20
    Hello again community, Just wondering if somebody knows how to write a formula that could look at a date in a date ordered column and place a date in another column that is greater by 30 days. From this point, I have an idea to have a for…
    Formulas and Functions205
    [email protected]
  • Kylie English
    Answered Kylie English 16 views 2 comments Most recent by Genevieve P
    First post 03/22/20,Last post 03/23/20
    Hi all, help please :) I have 3 columns - Approval Due Date Requested Date Priority Step 1 - Approval Due Date to look at the Priority Priority will be High, Moderate or Low / High = 7 days /Moderate = 14 days / Low = 21 days THEN Lo…
    Formulas and Functions162
    Kylie English
  • mel.greenspan
    Accepted Answer mel.greenspan 14 views 3 comments Most recent by Andrée Starå
    First post 03/20/20,Last post 03/22/20
    Is there a way to add values to a multi-select column using a formula? I have several sheets that i want to switch from checkbox columns to a single multi-select column "Subscribed Calendars". It is VERY manual right now doing it…
    Formulas and Functions143
    mel.greenspan
  • Michael Pichler
    Accepted Answer Michael Pichler 8 views 3 comments Most recent by Andrée Starå
    First post 03/22/20,Last post 03/22/20
    https://us.v-cdn.net/6031209/uploads/034/B5HVBCHN3QWJ.png I tried it with countif and it worked in Excel in German with the following formula: =ZÄHLENWENN(A2:A13;"<"&HEUTE()) as Example. What do I have to do in smarthsheet…
    Formulas and Functions83
    Michael Pichler
  • dskpetersen
    Accepted Answer dskpetersen 47 views 5 comments Most recent by Andrée Starå
    First post 01/30/20,Last post 03/21/20
    Trying to count in coumns how many boaxes are checked per column - Keep getting Boolean Expected. There are the formulas I have tried: =COUNTIFS([Mike M]5:[Mike M]135, 1) =COUNTIF([Mike M]5:[Mike M]135, 1) Is it possible to created this …
    Formulas and Functions475
    dskpetersen
  • Drew Frazelle
    Answered Drew Frazelle 35 views 4 comments Most recent by Andrée Starå
    First post 02/14/20,Last post 03/21/20
    I'm trying to count the number of unique pieces of equipment that are on my worksite. Each unit has a specific Unit Number listed in a sheet. Units that are On-Site are indicated by a green dot in another column. The tricky part for me is,…
    Formulas and Functions354
    Drew Frazelle
  • Greg Hjertager
    Greg Hjertager 17 views 8 comments Most recent by Andrée Starå
    First post 03/20/20,Last post 03/21/20
    I'm trying to use cell linking but it won't let me link a cell that doesn't have data in it yet. My plan is to have it link to a cell that will have data put in it gathered from a form. The end result is that I'll have two sheets. The fi…
    Formulas and Functions178
    Greg Hjertager
  • Jennifer Molina
    Answered Jennifer Molina 13 views 1 comment Most recent by Andrée Starå
    First post 03/21/20,Last post 03/21/20
    To give a little bit of context, I am creating a report where I can count whether a cell has been filled with information. The column I am using is a drop down list with 5 values. Let's say these values are apples, bananas, oranges, pears,…
    Formulas and Functions131
    Jennifer Molina
  • Amanda Chu
    #INCORRECT ARGUMENT SETAccepted Answer
    Accepted Answer Amanda Chu 656 views 17 comments Most recent by Andrée Starå
    First post 03/13/19,Last post 03/21/20
    Hello, I cannot figure out why this SUMIFS is not working. When I break up the SUMIF criteria, they both work fine. But when I put them together, I get #INCORRECT ARGUMENT SET error message. =SUMIFS({MH Client Profiles v2 Range 13}, &quo…
    Formulas and Functions65617
    Amanda Chu
  • Mark Silvester
    Answered Mark Silvester 13 views 5 comments Most recent by Paul Newcome
    First post 03/20/20,Last post 03/20/20
    The spreadsheet in question is a shipping/inventory database.  https://us.v-cdn.net/6031209/uploads/497/1R9QZYQQS423.png The top rows where Device SKUs are listed are the total contracted amount of devices a client pays for. Then as we cre…
    Formulas and Functions135
    Mark Silvester
  • Alexa Rice
    Answered Alexa Rice 17 views 6 comments Most recent by Paul Newcome
    First post 03/20/20,Last post 03/20/20
    I am working on an extended process and I want to sub-categorize a set of steps. Ideally I want to use the 1.1, 1.2, 1.1.1 as step headers but when I try and do functions to count cells it is not working. Example: https://us.v-cdn.net/…
    Formulas and Functions176
    Alexa Rice
  • coryg
    LOG FunctionAnswered Question
    Answered coryg 11 views 1 comment Most recent by Paul Newcome
    First post 03/20/20,Last post 03/20/20
    Is it possible to calculate (or even approximate) a LOG function in Smartsheet? We regularly use the below formula to calculate A/E fees: (35/LOG(Construction Cost))-1.15)*(Complexity Factor) = A/E fee % I have a sheet setup where someon…
    Formulas and Functions111
    coryg
  • Janet Skjerven
    Accepted Answer Janet Skjerven 16 views 3 comments Most recent by Paul Newcome
    First post 03/20/20,Last post 03/20/20
    Hello, I have a COUNTIFS formula (below) that returns the number of items that pass all the tests. Basically, count an item if it's unique id is not blank, it's % complete is 100, and the risk area is Antiboycott. This formula works beau…
    Formulas and Functions163
    Janet Skjerven
  • Jörg Schmidt Novum Hospitality
    Accepted Answer Jörg Schmidt Novum Hospitality 16 views 1 comment Most recent by Paul Newcome
    First post 03/19/20,Last post 03/20/20
    Hallo, I want to calculate the average damage without considering the maximum damage. I tried this: =AVERAGEIF(Zahlung4:Zahlung140; >1) - MAX(Zahlung4:Zahlung140) but it doesn´t work. I get a - value Any idea ? 🙄
    Formulas and Functions161
    Jörg Schmidt Novum Hospitality
  • alcapps
    Accepted Answer alcapps 9 views 3 comments Most recent by Paul Newcome
    First post 03/20/20,Last post 03/20/20
    I have a formula that counts absences for the past 6 months. A formula in another column looks at the first and when that number reaches 3, it adds an occurrence (value changes from 0 to 1). I've set up automation to notify the supervisor …
    Formulas and Functions93
    alcapps
  • Preston
    Different types of zeros?Accepted Answer
    Accepted Answer Preston 13 views 5 comments Most recent by Paul Newcome
    First post 03/19/20,Last post 03/20/20
    The formula below is creating multiple "zeros". See pictures. What am I missing? https://us.v-cdn.net/6031209/uploads/587/DYEAMAQ4PA89.png https://us.v-cdn.net/6031209/uploads/413/EOJGS05QYULC.png =IF([email protected] = "ERROR&…
    Formulas and Functions135
    Preston
  • Tiera Mack
    IF(OR with RYGGAccepted Answer
    Accepted Answer Tiera Mack 25 views 7 comments Most recent by Paul Newcome
    First post 03/18/20,Last post 03/20/20
    I would like to automate a RYGG column, assuming that my team is entering there own start and finish dates but due dates will be created by senior staff. Yellow=In progress Red=Overdue Grey = Not started Green=Completed So IF start date …
    Formulas and Functions257
    Tiera Mack
  • Madison McGuire
    Answered Madison McGuire 21 views 3 comments Most recent by Paul Newcome
    First post 03/19/20,Last post 03/20/20
    I have a spreadsheet with a calculated column (If statement based on previous column). I regularly paste in new rows, but the calculated columns do not always auto-populate. When I hover over the calculated cell, the calculations are prese…
    Formulas and Functions213
    Madison McGuire
  • MJLaughlin
    SUMIFSAccepted Answer
    Accepted Answer MJLaughlin 21 views 3 comments Most recent by Paul Newcome
    First post 03/19/20,Last post 03/20/20
    I am trying to create a SUMIFS formula that will allow me to pull data from the following columns: Region: APAC (There are several offices that make up the APAC region) OfficeStatus: Closed OfficeHeadCount: Sum Total (For example 8 APA…
    Formulas and Functions213
    MJLaughlin
  • BTeeter
    Accepted Answer BTeeter 19 views 3 comments Most recent by Paul Newcome
    First post 03/19/20,Last post 03/20/20
    =IF(AND([Logo Attached]3 = 1, [Lobby Sign]3 = 1, [Column Sign]3 = 1, Mailbox3 = 1, "Green"), IF(OR([Logo Attached]3 = 1, [Lobby Sign]3 = 1, [Column Sign]3 = 1, Mailbox3 = 1), "Yellow", "Red")) Formula above. …
    Formulas and Functions193
    BTeeter