counting a name that appears in a multi select dropdown

ShaunW

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

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

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å

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!

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å

@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!