Count Collect Contains formula

CBlass

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!

Paul Newcome

Try this:

=COUNTIFS({Survey Intake Name}, {Days Remote}, CONTAINS([Answer1]3, @cell))