Formulas and Functions

Formulas and Functions

Discussion List

  • Jamy Crum
    Accepted Answer Jamy Crum 13 views 3 comments Most recent by Paul Newcome
    First post 07/08/20,Last post 07/08/20
    Hi, I'm trying to create a nested formula used in a ticketing system that will total up active status (Open or In Progress) compared to location. Ticket Active Status for Location = Location <and> Status [Open <or> In Progress…
    Formulas and Functions133
    Jamy Crum
  • Elena Codrea
    Ref SUMIF example in HelpAccepted Answer
    Accepted Answer Elena Codrea 17 views 4 comments Most recent by Elena Codrea
    First post 07/05/20,Last post 07/08/20
    Hi, Referring the SUMIF example in the Help article https://help.smartsheet.com/function/sumif, it is not clear to me why =SUMIF([Price Per Unit]1:[Price Per Unit]3, >20, [Transaction Total]1:[Transaction Total]3) has the result 3,115.0…
    Formulas and Functions174
    Elena Codrea
  • Susan Hildebrant
    Accepted Answer Susan Hildebrant 10 views 3 comments Most recent by Paul Newcome
    First post 07/08/20,Last post 07/08/20
    Here is the formula I used: =DATE(YEAR(Date3), MONTH(Date3) + 1). It works great for dates up to the 12th month (December). How do I modify the formula to go to January of the next year, e.g. 12/01/20 to 01/01/21? Thanks, Susan (Image)
    Formulas and Functions103
    Susan Hildebrant
  • Stephanie Bauder
    Answered - Pending Review Stephanie Bauder 9 views 3 comments Most recent by Paul Newcome
    First post 07/08/20,Last post 07/08/20
    My formula comes back as #No Match but when I filter, I see the results I need. I am hoping you can assist! I am trying to calculate the amount of time spent for a contact person. Here is the formula I have so far: =SUMIF([Contract Phase…
    Formulas and Functions93
    Stephanie Bauder
  • Josh Canady
    Answered - Pending Review Josh Canady 12 views 6 comments Most recent by Josh Canady
    First post 07/08/20,Last post 07/08/20
    Can you change formulas to make "Not Started" be the same as if it was blank? If I deleted the Not Started lines then my parent row would match my child/ children rows. (See snip # 2). I'm not sure if it's possible since you can'…
    Formulas and Functions126
    Josh Canady
  • weswillis
    Accepted Answer weswillis 36 views 3 comments Most recent by Paul Newcome
    First post 07/08/20,Last post 07/08/20
    Hoping someone can help with this as it seems possible... I have a sheet for contacts at individual centers across a state. There are roles assigned to the contacts, so for example there may be a director, a supervisor and a coordinator. …
    Formulas and Functions363
    weswillis
  • Mallory McFall
    Accepted Answer Mallory McFall 11 views 5 comments Most recent by Andrée Starå
    First post 07/07/20,Last post 07/08/20
    Hello, I am wanting to count the number of employees that are working this week. However my current formula does not take into account the number of unique names within a weeknumber. Any help would be apreciated.
    Formulas and Functions115
    Mallory McFall
  • Steph Harper
    Accepted Answer Steph Harper 11 views 4 comments Most recent by Steph Harper
    First post 07/07/20,Last post 07/08/20
    Hi all! I'm trying to summarize deliverables using the date stamp of when a form was submitted. Unfortunately the way our form is set up, we have employees fill out a form and enter the month and the year separately so our sheet does not h…
    Formulas and Functions114
    Steph Harper
  • Brett Kennedy
    Accepted Answer Brett Kennedy 13 views 3 comments Most recent by Paul Newcome
    First post 07/08/20,Last post 07/08/20
    Hi all, This is my current formula, and it works except that if I have more than the name in the formula also checked in the multi select box it does not count it. How do I change this formula so it counts the name in the multi select box …
    Formulas and Functions133
    Brett Kennedy
  • Prajna Jain
    Answered - Pending Review Prajna Jain 13 views 1 comment Most recent by Andrée Starå
    First post 07/08/20,Last post 07/08/20
    Is there any way that I can ensure that at the time of copying the formula the cell reference doesn't change? Thanks Prajna
    Formulas and Functions131
    Prajna Jain
  • SYSPK
    SYSPK 894 views 25 comments Most recent by Paul Newcome
    First post 11/19/18,Last post 07/08/20
    I'd like to get a list of unique values in a range. I'm finding a lot on the community about how to flag duplicates, but not sure how to get a list where each unique value appears in a cell. Screenshot below is what I'm looking for. My goa…
    Formulas and Functions89425
    SYSPK
  • KLM
    Accepted Answer KLM 9 views 2 comments Most recent by Genevieve P
    First post 07/08/20,Last post 07/08/20
    Hi all - I'm looking to build out a repeatable process calendar. We have something starting (Intake) on X day, and then a series of activities that take a certain amount of time. I'm looking to replicate the Excel function of (Date + X da…
    Formulas and Functions92
    KLM
  • LisaB:-)
    Accepted Answer LisaB:-) 10 views 3 comments Most recent by Paul Newcome
    First post 07/08/20,Last post 07/08/20
    Hi all! I would like my RYG balls to express the following (actual dates, not cell reference): Green = before 21 August 2020 Yellow = after 21 August 2020 Red = after 28 August 2020 This is my current formula, and although individual state…
    Formulas and Functions103
    LisaB:-)
  • Michael Murphy
    Accepted Answer Michael Murphy 17 views 3 comments Most recent by Genevieve P
    First post 07/04/20,Last post 07/08/20
    I am looking to understand how the COLLECT() function nested within the AVG() function works and is evaluated. Using the help files, I found the following example. I am specifically looking at the very last formula where the result is &qu…
    Formulas and Functions173
    Michael Murphy
  • Matt Foss
    Accepted Answer Matt Foss 16 views 4 comments Most recent by Matt Foss
    First post 07/06/20,Last post 07/08/20
    I've been able to find close approximations to what I'm looking for, but have one missing piece. I have a summary sheet ("summary") that pulls data from another source form evaluation sheet ("source"). Essentially, I ha…
    Formulas and Functions164
    Matt Foss
  • Jonathan Kearns
    Answered - Pending Review Jonathan Kearns 17 views 3 comments Most recent by Paul Newcome
    First post 06/30/20,Last post 07/08/20
    I am trying to setup some sheet automation as follows: Program Manager sheet: Tracking ID is auto-generated using auto-numbering When a new tracking ID is generated would like to populate that new ID into another Master worksheet into next…
    Formulas and Functions173
    Jonathan Kearns
  • dskpetersen
    Answered - Pending Review dskpetersen 8 views 1 comment Most recent by Paul Newcome
    First post 07/07/20,Last post 07/08/20
    I need a formula that I'm not sure I can do. I have tried many different things, and only have a portion working. I need to be able to determine the "difference" between our $$ bid and either the low bidder, if we are not, or t…
    Formulas and Functions81
    dskpetersen
  • Doyle54
    Answered - Pending Review Doyle54 27 views 6 comments Most recent by Andrée Starå
    First post 06/25/20,Last post 07/08/20
    (Image)
    Formulas and Functions276
    Doyle54
  • Susan Swisher
    Answered - Pending Review Susan Swisher 22 views 2 comments Most recent by Genevieve P
    First post 07/07/20,Last post 07/08/20
    I need a formula to look at @column1/cell (whatever it is); go into another sheet, find that match/@column1/cell, and then return what is in another column (in the 2nd sheet). Hopefully, that isn't confusing............
    Formulas and Functions222
    Susan Swisher
  • Ameena Bossier
    Accepted Answer Ameena Bossier 37 views 3 comments Most recent by Andrée Starå
    First post 06/25/20,Last post 07/08/20
    Hey All, Reaching out to the masses to hopefully get some help. I need to pull information from sheet 1 into sheet 2 using the INDEX  and MATCH functions. Sheet 1 that I want to pull information from has: Child rows Task Name column (The …
    Formulas and Functions373
    Ameena Bossier
  • Tammy Parker
    Answered - Pending Review Tammy Parker 15 views 3 comments Most recent by Andrée Starå
    First post 07/07/20,Last post 07/08/20
    Hello I have 5 sheets of many cells of data for different work groups within my organization. I need to create ONE summary sheet that totals each of the cells from the 5 sheets. The only way I can figure out how to do it is by creating th…
    Formulas and Functions153
    Tammy Parker
  • Syed Mohd Muhafzal
    HAS Functions Returning 0Accepted Answer
    Accepted Answer Syed Mohd Muhafzal 12 views 4 comments Most recent by Andrée Starå
    First post 07/02/20,Last post 07/08/20
    Hi Smartsheet Users, I am struggling with the formula HAS with multiple selection drop down values and hoping I could get some guidance here. Here, i would like to count how many "swimming" classes I have in a week. (Image) The…
    Formulas and Functions124
    Syed Mohd Muhafzal
  • Rob Myer
    Drop Down ListAccepted Answer
    Accepted Answer Rob Myer 14 views 5 comments Most recent by Andrée Starå
    First post 07/03/20,Last post 07/08/20
    Q: Is it possible to assign a drop-down list to a single cell and not the entire column?
    Formulas and Functions145
    Rob Myer
  • lcamacho19911
    Accepted Answer lcamacho19911 5.3K views 30 comments Most recent by Genevieve P
    First post 10/03/19,Last post 07/08/20
    Hi The drop-down multi select is very nice and useful.  I started to use it yesterday, but I need to use the formula =countif(range, criterion).  I want to count how many times a value is repeated in a range that has multi values. I use …
    Formulas and Functions531730
    lcamacho19911
  • Scott Rae
    Answered - Pending Review Scott Rae 53 views 10 comments Most recent by Andrée Starå
    First post 01/31/20,Last post 07/08/20
    Hi, I'm looking to see if there is any formula(or anything else) I could use to change a cell to a specific pre-defined value, if a different cell is CHANGED TO a certain value. I know that the changed to functionality is available through…
    Formulas and Functions5310
    Scott Rae
  • Ryan Michalski
    Answered - Pending Review Ryan Michalski 23 views 3 comments Most recent by Genevieve P
    First post 06/30/20,Last post 07/08/20
    I use Dynamic View. Dynamic View, unfortunately, only allows 1 single column to be the list of users who can see data in a specific row. Smartsheet is not able to combine multi-contact lists into a single column that is recognized within…
    Formulas and Functions233
    Ryan Michalski
  • Joachim Chenna
    Answered - Pending Review Joachim Chenna 17 views 3 comments Most recent by Andrée Starå
    First post 07/07/20,Last post 07/08/20
    Hello, I have question do you know how I can use the same "formulas" for each new ligne generate by the form ? The formulas has the purpose to take information from another cell. MT for your help. I tried this tutorial but it d…
    Formulas and Functions173
    Joachim Chenna
  • UrbanNomad
    Answered - Pending Review UrbanNomad 27 views 3 comments Most recent by Ben G
    First post 06/25/20,Last post 07/07/20
    Hi All, I want to allocate hours equally based on a given date range, in excel I could get it work using Eomonth, in Smartsheet, I'm struggling to achieve. I have attached the image of the formula I got it working in Excel, how do I replic…
    Formulas and Functions273
    UrbanNomad
  • Rigoberto Huizar
    Answered - Pending Review Rigoberto Huizar 25 views 2 comments Most recent by Rigoberto Huizar
    First post 07/06/20,Last post 07/07/20
    I am having trouble with my formula. I'm a newbie and my logic be flawed and terrible. Learning in progress.  I thought I had received the answer but as it turns out I was still trying to acquire more information.  Essentially what I want …
    Formulas and Functions252
    Rigoberto Huizar
  • Gabriel Perez-Pujols
    Accepted Answer Gabriel Perez-Pujols 22 views 3 comments Most recent by Paul Newcome
    First post 07/07/20,Last post 07/07/20
    Hello! I am relatively new to Smartsheet and I am still in the process of learning excel. One of my sheets is a simple data entry sheet in which I need the Sheet Summary to show the information in the most recent row. I have been using the…
    Formulas and Functions223
    Gabriel Perez-Pujols