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

  • Help w/ IF formula
    In Formulas and Functions
    Hi team – I have the following columns:   I want to only have the RISK AGE column show a number (count of days) if the STATUS = ACTIVE.  If the STATUS = INACTIVE then the RISK AGE column needs to be blank.  Column configurations: STATUS – …
    jmo Profile Photo jmo
    Answered - Pending Review
    10 1
    Answered - Pending Review 10 views 1 comment Most recent by Andrée Starå
  • CountIfs one column is not blank and the other one is
    In Formulas and Functions
    I'm trying to count rows that have a date in one cell but is blank (no checkmark) in the other. Is that possible? I've done multiple iterations, all unsuccessfully. Here's the last one I've tried: =COUNTIFS([Date Tip Sheet Submitted]1:[Da…
    Answered - Pending Review
    20 2
    Answered - Pending Review 20 views 2 comments Most recent by Jodi Steele
  • Comments in a Cell?
    In Formulas and Functions
    I have a feeling the answer is no but is it possible to add a comment to a specific cell as Excel does or can you only add a comment to a row? Much appreciated.
    Answered - Pending Review
    12 1
    Answered - Pending Review 12 views 1 comment Most recent by Andrée Starå
  • Including only NETWORKDAYS in Aging
    In Formulas and Functions
    Looking for some help finding the aging for ticket resolution, in workdays. I can't seem to find a workable solution to add to my working formula below, which finds the count between either Today's date and the Created date OR Created and…
    Accepted
    9 2
    Accepted Answer 9 views 2 comments Most recent by Cristina Parker
  • Using the Average(Collect) formula
    In Formulas and Functions
    I am trying to create a formula that will calculate the average days of a column if the FY column = FY2021 This is the formula that I am using but is keeps giving me an error This is the data sheet I am using: Any ideas how to get this t…
    Answered - Pending Review
    10 3
    Answered - Pending Review 10 views 3 comments Most recent by Paul Newcome
  • Calculating percentage based on status options across multiple columns
    In Formulas and Functions
    I am trying to average the sum across multiple columns. Each column is a single select dropdown that will allow the user to choose from the following options: ⦸ (Not applicable) ◷ (in progress) ✓ (Complete) I want to assign val…
    Answered - Pending Review
    12 2
    Answered - Pending Review 12 views 2 comments Most recent by John Hageman
  • COUNTIF or COUNTIFS formula for a date column, criteria based off MONTH and YEAR
    In Formulas and Functions
    I am trying to total the number of completed submissions on my Ankeny Repair spreadsheet based off of a specific month from a specific year. Range 4 is the column with the completed dates. My current formula is this... =COUNTIFS(MONTH({Ank…
    Accepted
    573 21
    Accepted Answer 573 views 21 comments Most recent by Paul Newcome
  • Getting #UNPARSEABLE after using IFERROR
    In Formulas and Functions
    Hi all, I've got this awesome formula (Thanks, Paul Newcome!), which calculates the difference in months between two dates: =(12 - MONTH([Actual Study End Date]@row) + (((YEAR([Expected Expiration Date]@row) - YEAR([Actual Study End Date]@…
    Accepted
    11 3
    Accepted Answer 11 views 3 comments Most recent by Paul Newcome
  • Combining 2 ISDATE(INDEX(MATCH arguments referencing 2 reference sheets
    In Formulas and Functions
    I am creating a new vendor request form that will copy rows to two separate, detailed sheets where I can track their unique requirements. From my detailed sheets, I want some information to flow back to the request sheet so those that requ…
    Answered - Pending Review
    20 2
    Answered - Pending Review 20 views 2 comments Most recent by Paul Newcome
  • Why does my range change?
    In Formulas and Functions
    Every time I complete a formula the word TEMPLATE appears in my range, but does not appear in my sum_range. Is that normal? =SUMIF({Template of Sidewalk Database Module Range 1}, "Completed", {Sidewalk Database Module Range 1})
    Answered - Pending Review
    15 2
    Answered - Pending Review 15 views 2 comments Most recent by Paul Newcome