counting a name that appears in a multi select dropdown

i am trying to count the amount of times a name appears in a sheet (Column) the formula below works fine if a cell only has one name however if a cell has multiple names it will not count it from that cell, the formula i am using is =COUNTIFS({SNOW - ARIaDd Log assigned to}, $[Primary Column]@row, {SNOW - ARIaDd Log type}, [Column2]$9, {SNOW - ARIaDd Log -status}, "Open").

i have tried adding the HAS function, =COUNTIFS({SNOW - ARIaDd Log assigned to}, HAS(@Cell, $[Primary Column]@row, {SNOW - ARIaDd Log type}, [Column2]$9, {SNOW - ARIaDd Log -status}, "Open") but that just returns #Unparesable error.

any guidance would be appreciated


Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hey @ShaunW

    I didn't check whether your formula was valid to count the names - only to clear your unparseable.

    @cell and @row are case sensitive. Your @cell in your formula needs to be written as lower case.

  • ShaunW
    ShaunW ✭✭✭✭

    Kelly,

    many thanks and good catch however changing the C to lower case only removed the parse error and then returns a "0" whereas it should return "12"

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

    Hi @ShaunW

    I hope you're well and safe!

    Haev you double-checked the cross-sheet references? What type of columns are you trying to count from the other sheet?

    Can you maybe share some screenshots of the ranges? (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 week!

    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.

  • ShaunW
    ShaunW ✭✭✭✭

    Hi,


    so i am trying to count the number of times a name appears in the "assigned to" column based on "type", when there is only one name in the cell it counts ok but when there are multiple names it doesn't count the occurance of that name (see Alicia Callan below) from those cells



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

    @ShaunW

    Try adding a so-called helper column (Contact Type) and use that for the HAS part.

    Otherwise, the name has to be an exact match to make it work.

    Make sense?

    Did that work/help?

    Remember! 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!