Formulas and Functions

Let the community help you with that tricky formula or function! See how others are utilizing the power of formulas and functions to save them time and create more powerful solutions with Smartsheet.

Discussion List

  • Complex formulas ammendment
    In Formulas and Functions
    Hi All, I have an existing Smartsheet that has some relatively complex formulas to count the the status of an action as there are several conditions for an action to show as "Open" and "Overdue". Over the last few days,…
    Accepted
    21 5
    Accepted Answer 21 views 5 comments Most recent by Paul Newcome
  • VLOOKUP Doesn't Appear To Work With Auto-Number Columns/Cells
    In Formulas and Functions
    I have a main sheet containing a list of projects with the first column containing the ID of the project which is configured as an auto-number column. I am working on setting up another sheet to track projects tasks and want to be able to …
    Answered - Pending Review
    31 1
    Answered - Pending Review 31 views 1 comment Most recent by Andrée Starå
  • Nested IF AND OR
    In Formulas and Functions
    I am trying to get a formula for a check box that combines an if AND with an If OR. I have a column labeled Rental Billed that is a check box. There is another column that has multiple status options from a drop down. I want the formula…
    Answered - Pending Review
    41 3
    Answered - Pending Review 41 views 3 comments Most recent by Sean Morgan
  • COUNTIF Help
    In Formulas and Functions
    Hello, Is there a way to count the number of times a contact appears in a certain column, if there are multiple contacts in some of the cells? (Image) For example, I can use COUNTIF to see how many times Charles appears in cells where it i…
    Answered - Pending Review
    11 1
    Answered - Pending Review 11 views 1 comment Most recent by Andrée Starå
  • The 25,000 cell reference limit is puzzling
    In Formulas and Functions
    Dear Smartsheet: I am a bit confused by the 25,000 cell reference limit.  In my specific case I am simply trying to VLOOKUP from a table where I have 700 rows and 17 columns.  That equals 11,900 cells which is less than 25,000 cells.  So…
    644 views 22 comments Most recent by Paul Newcome
  • Manual % complete formula in the parent row
    In Formulas and Functions
    I am not using % complete automation in the parent row as some sub tasks do not have dates. However, using =AVG(CHILDREN()) in the parent row I get an incorrect % complete. The actual %complete should be 26%. Do I need to use a different f…
    Accepted
    52 5
    Accepted Answer 52 views 5 comments Most recent by Stevefc
  • Multiple IF/AND for duration and status
    In Formulas and Functions
    Hi Community, I am trying to structure an IF/AND formula to reflect the following conditions: IF [Duration]@row <> 0, AND IF [Complete]@row =0, "Not Started" IF [Duration]@row <> 0, AND IF [Complete]@row =0, AND IF[Co…
    Accepted
    21 2
    Accepted Answer 21 views 2 comments Most recent by Alessandro Terranova
  • Parent cell - Status symbol total
    In Formulas and Functions
    (Image) Hi there, How can the parent cell (circled in blue) automatically reflect the correct color/symbol concerning the tasks below? When I say automatically, I refer to do it by default, not me selecting the symbol all the time. Can th…
    Answered - Pending Review
    21 1
    Answered - Pending Review 21 views 1 comment Most recent by Ryan Nix
  • Create a Duplicate Flag
    In Formulas and Functions
    Hello, I am trying to create a flag that turns red when a duplicate name is entered. Here is the formula I have so far, but it keeps telling me it is "#Unparseable" =IF(COUNTIF([Client/Resident Name]:[Client/Resident Name]@cell)…
    Accepted
    21 1
    Accepted Answer 21 views 1 comment Most recent by Maria Watters
  • COUNTIFS Formula
    In Formulas and Functions
    I am fairly new to working with formulas. I have a Project Status column that contains several values with "In Progress". For example, In Progress - Scoping, In Progress - Development, etc. I would like to count all the instances…
    Accepted
    11 3
    Accepted Answer 11 views 3 comments Most recent by Andrée Starå