Looking for a function that will count if multiple cells have 1 of 3 values

Options

I have a Sheet that has a types: Next Release, Testing, In Development, Bug Fix, Backlog, and NEW. I am looking for a function to add to my summary that only counts In Progress types such as "Testing" and "In Development".

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Troy G

    I hope you're well and safe!

    Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

    I hope that helps!

    Be safe, and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Troy G
    Troy G ✭✭
    Options

    Here is a screen capture of my issues. Again I have a column titled "Type" that column is a drop down to show what type of enhancement it is for example Site Request, Not Prioritized (New), Developer idea, Ice Box. These types also control our card view lanes. When we assign these tasks and move from the backlog we change the type throughout its development stages examples In Development, Testing, Next Release, Completed, Bug Fix. I am needing a function for my summary panel that would count all the "Types" that we consider "In Progress" like Testing, Next Release, Bug Fix, and In Development. In addition, I would apply this same function to count all backlog items the have type Site request, Developer Idea, and Ice Box.

    I do have In Progress and Backlog as a Parent and I found using the functions below gets me what I need but I can not always depend on the correct types being under the correct parent.

    =COUNT(CHILDREN(Name1))

    =COUNT(CHILDREN(Name21))

  • ShelbyWarren
    ShelbyWarren ✭✭✭✭✭
    edited 04/21/23
    Options

    @Troy G One solution is you could add a "Status" column dropdown list that contains the verbiage you need (Not Started, In Progress, Backlog, etc.). Then you could create reports that categorize the rows similar to how you have them in your sheet.

    Then you could create formulas that count the "Types" that have a certain "Status"

    =Countifs(Type:Type, "Backlog", Status:Status, "In Progress"

    This formula could be simplified in a metrics sheet, but these are some ideas to get the ball rolling

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!