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!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!