Count Collect Contains formula
I'm trying to create a metric sheet to compile results from a number of survey questions. For questions where there is one answer per question, this formula works fine:
=COUNT(COLLECT({Survey Intake Name}, {Days Remote}, [Answer1]3))
{Survey Intake Name}is the base value to count, {Days Remote} is the reference to the question column, and [Answer1]3 is the column header on the metric sheet that matches the answer we want to count.
When there are multiple answers possible in a cell, this formula does not work. I've tried using a combination of count/collect/contains and countif/contains but keep getting 1. Any thoughts on how to count all rows where [Answer1]3 shows up as a value even if there are also other values in that same cell? Hoping someone has experience with a similar scenario. Thanks in advance!
Answers
-
Try this:
=COUNTIFS({Survey Intake Name}, {Days Remote}, CONTAINS([Answer1]3, @cell))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!