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

  • diegoartorres
    diegoartorres 172 views 6 comments Most recent by Paul Newcome
    First post 10/19/18,Last post 01/13/20
    Hello everyone, I am looking to have an information sheet that is looking for several criteria with "and" and "or". For example, I want the formula to count if the other sheet has "Priority 1" and "John…
    Formulas and Functions1726
    diegoartorres
  • Mike Banom
    Answered Mike Banom 57 views 3 comments Most recent by Paul Newcome
    First post 01/10/20,Last post 01/13/20
    Hello, I'm trying to setup the following: • Time tracking spreadsheet with the following columns: Date, Client, Project, Time (increments of .25 hrs) • either in the same sheet or different: Ability to either loop through each row and spit…
    Formulas and Functions573
    Mike Banom
  • cthrasher97141
    Answered cthrasher97141 26 views 3 comments Most recent by Andrée Starå
    First post 01/08/20,Last post 01/12/20
    Hello I would like to add multiple contacts to a single contact cell for one task with a formula, is there a away to do this? Can Group Management be utilized? Thank you
    Formulas and Functions263
    cthrasher97141
  • Zachary Mitchell
    Answered Zachary Mitchell 26 views 3 comments Most recent by Andrée Starå
    First post 01/09/20,Last post 01/12/20
    I've looked around and I realize that this is a feature many people want within smartsheet. I was wondering if anyone has a workaround for this. I want to automate the start date to today but I don't want it to change so using =TODAY () wo…
    Formulas and Functions263
    Zachary Mitchell
  • Tina
    Accepted Answer Tina 21 views 3 comments Most recent by Andrew Stewart
    First post 01/09/20,Last post 01/12/20
    Hello, I am trying to create a formula and I need some help. The formula would be in a column which have a single select dropdown option: Update. The formula would use information from two columns. The column names are Level and Late…
    Formulas and Functions213
    Tina
  • Michael ROBASTON
    Accepted Answer Michael ROBASTON 19 views 9 comments Most recent by Andrée Starå
    First post 01/12/20,Last post 01/12/20
    Hi community I'm trying to do something pretty easy but couldn't go further on my own. I have one column where users from one site will answer in every cells "Yes, maybe, no" through a 3 choices list (column Baudouin) that show…
    Formulas and Functions199
    Michael ROBASTON
  • Owen Smith
    First post 01/11/20,Last post 01/11/20
    I have sheet 1 with cell A and cell B, I would like to manually enter cell B and have cell A auto populate by searching/referencing sheet 2. best, Owen
    Formulas and Functions133
    Owen Smith
  • Allison Jones
    Accepted Answer Allison Jones 32 views 6 comments Most recent by Andrée Starå
    First post 01/08/20,Last post 01/11/20
    The last 24 hours have been a nightmare. I have been doing maintenance on some of our data sheets which have formulas and that link to roll up sheets. The cells are taking a long to to update -and in some cases haven't updated at all (i.e…
    Formulas and Functions326
    Allison Jones
  • Tania Nassar
    Answered Tania Nassar 25 views 6 comments Most recent by Mike Wilday
    First post 01/10/20,Last post 01/10/20
    Hello, I would like the "Bid Back" date to highlight in red if it is past the date in red in the Trade column. I would also like the "Bid Back" date to highlight in Yellow if it is within 3 days of the red date in the t…
    Formulas and Functions256
    Tania Nassar
  • rymurph
    Accepted Answer rymurph 21 views 9 comments Most recent by Paul Newcome
    First post 01/10/20,Last post 01/10/20
    Looking for formula help with RYG referencing a date column. I want a green ball if the expiration date is more than a year away, yellow ball for expiration between today and 365 days and red for any date in the past.
    Formulas and Functions219
    rymurph
  • Jeffrey Wagemans
    Accepted Answer Jeffrey Wagemans 28 views 4 comments Most recent by Paul Newcome
    First post 01/10/20,Last post 01/10/20
    Hi All, I'm using a formula to count how many stores we have opened in a certain country/month. This information is then used in a chart in a dashboard. The problem is that when we haven't opened any store it shows a zero. This zero is al…
    Formulas and Functions284
    Jeffrey Wagemans
  • Mike Jones
    Accepted Answer Mike Jones 20 views 5 comments Most recent by Paul Newcome
    First post 01/10/20,Last post 01/10/20
    I have added the number of closed projects to a report by getting the total from the "Status" column in another sheet, like this. =COUNTIF({ARC Range 2}, "Closed") There is also a "Date" column in the sheet.…
    Formulas and Functions205
    Mike Jones
  • Ray B
    Accepted Answer Ray B 16 views 3 comments Most recent by Paul Newcome
    First post 01/10/20,Last post 01/10/20
    Good Day All: I am trying to modify a basic Formula to Auto Checkbox the Parent only if All the Children / Decedents are Checked. =COUNTIFS(DESCENDANTS(), 1) Thanks
    Formulas and Functions163
    Ray B
  • movieloverron
    movieloverron 29 views 3 comments Most recent by Paul Newcome
    First post 07/24/19,Last post 01/10/20
    Getting an unparseable error when i do this: =COUNTIFS(DESCENDANTS([Final and Locked]824, 1)) Basically, I just want it to automatically count the number of checkboxes in the next column so when new children are added by other users, I d…
    Formulas and Functions293
    movieloverron
  • beth_linder28141
    Accepted Answer beth_linder28141 27 views 9 comments Most recent by Paul Newcome
    First post 01/09/20,Last post 01/10/20
    I need help. I can get the if/and statement to work, BUT if the cell is blank it gives one of the options. =IFERROR(IF(AND([Bottom OP]1 >= [Spec - Low]1, [Bottom OP]1 <= [Spec - High]1)), "In Spec", "Out of Spec"…
    Formulas and Functions279
    beth_linder28141
  • JoAnn Merritt
    JoAnn Merritt 43 views 9 comments Most recent by Paul Newcome
    First post 07/12/19,Last post 01/10/20
    I have a start time and end time in my smartsheet but need to calculate hours worked.  Has anyone used a formula before to do this? if so can you pass along?
    Formulas and Functions439
    JoAnn Merritt
  • MCorbin
    Answered MCorbin 26 views 3 comments Most recent by Paul Newcome
    First post 01/09/20,Last post 01/10/20
    I am trying to find a way to allow users to select multiple criteria, where each criteria has a score. And once selected, have a column add up the scores for all criteria selected. For Example: Criteria Score Cat …
    Formulas and Functions263
    MCorbin
  • Nasir@EBC
    Accepted Answer [email protected] 27 views 3 comments Most recent by [email protected]
    First post 01/10/20,Last post 01/10/20
    Hi all I am using the following formula in a parent row which has 10 child rows: =IF(COUNTIF(CHILDREN(), "Complete") < 10, "Not Done", "Done") It shows Done as soon as all the children rows have the word Co…
    Formulas and Functions273
    [email protected]
  • Tina
    Accepted Answer Tina 13 views 2 comments Most recent by Paul Newcome
    First post 01/10/20,Last post 01/10/20
    Hi, I am using this formula =IF(AND(NOT(ISBLANK([Latest Update]@row)), [email protected] = 2), "Update", "") and I would like to add =IF([email protected] = 1, "Project Update") if it is Not Project Update it it needs to b…
    Formulas and Functions132
    Tina
  • fennerb
    Answered fennerb 30 views 14 comments Most recent by Paul Newcome
    First post 01/02/20,Last post 01/10/20
    Hello, I'm trying to find out how many requests a lawyer gets per month based on a few things: -AFID (Auto Fill-In Date) -Type of Request = FOIA -Lawyer's Staff name -Status (Red, Yellow, Green, Blue; However, these are not in as words, th…
    Formulas and Functions3014
    fennerb
  • Brian Erdelyi
    Answered Brian Erdelyi 39 views 4 comments Most recent by Andrée Starå
    First post 01/05/20,Last post 01/10/20
    I am trying to dashboard my annual projected revenue. This will factor on the amount of the job and the expected completion date of the job. I know the scheduled job date and the expedted amountof time it takes to complete a job so it seem…
    Formulas and Functions394
    Brian Erdelyi
  • Lee Ratzlaff
    Accepted Answer Lee Ratzlaff 30 views 1 comment Most recent by Andrew Stewart
    First post 01/09/20,Last post 01/10/20
    I am working on creating a tracking sheet for a longitudinal study, where clients need to be re-interviewed every six months until they are discharged from the program. I have a status field for each time point, and have used conditional f…
    Formulas and Functions301
    Lee Ratzlaff
  • @emipathy
    Answered @emipathy 20 views 3 comments Most recent by Andrew Stewart
    First post 01/09/20,Last post 01/09/20
    Hi All, I've been tasked with finding a way to automate a column in a health-tracker for our PMO. Here is the ask: "When [Health]@row changes to Any Value (red/yellow/green/grey), update the [Date of Change]@row with the date the ch…
    Formulas and Functions203
    @emipathy
  • Donnym
    Answered Donnym 22 views 4 comments Most recent by Donnym
    First post 01/09/20,Last post 01/09/20
    We have created a sign in form for our business. When a customer arrives they fill out the form. In the form is a dropdown of different areas of our business. We are trying to count on a sheet how many times an area in the dropdown has bee…
    Formulas and Functions224
    Donnym
  • Alejandra
    Closed Alejandra 1 view 0 comments Started by Alejandra
    First post 01/09/20,Last post 01/09/20
    This discussion has been moved.
    Formulas and Functions10
    Alejandra
  • Cristian Rangel
    Number to Text FormulaAccepted Answer
    Accepted Answer Cristian Rangel 33 views 2 comments Most recent by Cristian Rangel
    First post 01/09/20,Last post 01/09/20
    hello everyone, i am trying to create a formula that will take a series of numbers (UPC) and convert them into a text. I am currently trying to implement the barcode scanner to a form. ex: on the form, i will scan the product/equipment, an…
    Formulas and Functions332
    Cristian Rangel
  • Yvonne "Buffy" Frongia
    Answered Yvonne "Buffy" Frongia 19 views 1 comment Most recent by Andrew Stewart
    First post 01/09/20,Last post 01/09/20
    Hi, I am a new user and I am trying to design a sheet for us. I am trying to write a formula to have the symbols return the following: If % complete = 100%, return gray; If target day is today and it is less than 100% complete, return Re…
    Formulas and Functions191
    Yvonne "Buffy" Frongia
  • Chamilla
    Accepted Answer Chamilla 24 views 5 comments Most recent by Paul Newcome
    First post 01/09/20,Last post 01/09/20
    I need to create a formula that concatenates (JOIN or "+") several fields in my sheet summary, back into a column in my sheet. What is the syntax I need to use to be able to reference sheet summary fields? Thanks!
    Formulas and Functions245
    Chamilla
  • rbuckner
    Accepted Answer rbuckner 25 views 5 comments Most recent by Paul Newcome
    First post 01/09/20,Last post 01/09/20
    I have formatted my RYG balls to change according to the status of the task; Not Started = Grey, In Progress = Yellow, Complete = Green, and so on. I want Red = Not completed, and also past due. This is what I have: =IF([email protected] = &quo…
    Formulas and Functions255
    rbuckner
  • Keith McCloskey
    Answered Keith McCloskey 27 views 8 comments Most recent by Paul Newcome
    First post 01/07/20,Last post 01/09/20
    Hello, I am looking at an existing sheet and trying to pull that information over to another sheet. The current column is utilizing a check box and I would like to pull that information over based on a specific number that is being utili…
    Formulas and Functions278
    Keith McCloskey