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
-
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.
-
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"
-
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.
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!