Counting specific values in multi select cells
Hi All,
I am trying to count how many offices have certain engagements (choices are Calls, Email, Weekly PF call, FB gorup, Other, Texts). These cells are linked cells from another sheet.
I tried these formulas:
=countif([Office Engagement:Office Engagement], contains("Calls", Office Engagement@row))
=countif([Office Engagement:Office Engagement], find("Calls", Office Engagement@row))
=countif([Office Engagement:Office Engagement], has(Office Engagement@row, "Calls))
and they are pulling 0 even though the choice is in the cell. I've tried to add "> 0" at the end of the countif formula, but then it only pulls how many cells total that are not blank.
Please advise.
Thank you!
Best Answer
-
No worries!
Happy to help!
Try something like this.
=COUNTIF([Office Engagement]@row, CONTAINS("Calls", @cell))
Did that work?
✅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.
Answers
-
Hi @bea.sun
I hope you're well and safe!
Do you have one column named Office Engagement and another one names OfficeEngagement?
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 Andree,
Sorry, there's only one column and its Office Engagement.
Thank you!
-
No worries!
Happy to help!
Try something like this.
=COUNTIF([Office Engagement]@row, CONTAINS("Calls", @cell))
Did that work?
✅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.
-
Yes, it did. But I want to count a range of cells, not just one cell. Is that possible?
-
I just tried it with a range of cells and it worked! I appreciate you, thank you!!
-
Excellent!
You're more than welcome!
✅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.
-
I am having a similar issue. I am trying to create a column on a new sheet that shows the team member count by project.
The Sheet that I am pulling from has a multi-select column as it is sometime necessary to have team members assigned to multiple projects due to schedules/rows.
I have tried multiple options, including the formula below
=COUNTIF([{Sterling QAQC Whiteboard 2.0 Range 1}]@row), CONTAINS("[Project]"@row)
Am I on the right track or should this perhaps be done via automation?
Thank you!
-
Hi @Mary Kirby
You're close!
With a multi-select column you'll want to use the HAS function to see if the cell has that selection.
It also looks like your references may be formatted a little incorrectly. For cross-sheet references, you only need the {reference} without the @row. The @row basically tells the formula to look in this row, but you want it to look in an entire column in another sheet.
Try this:
=COUNTIF({Sterling QAQC Whiteboard 2.0 Range 1}, HAS(@cell, Project@row))
Here are some Help Articles that you may find useful:
- HAS Function
- Create Efficient Formulas with @cell and @row
- Create cross sheet references to work with data in another sheet
- Create a Cell or Column Reference in a Formula
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Perfect! Thanks a million!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!