How to find 2 values from a multi-select column for reporting

Justin Relitz

I am trying to count how many times in a smartsheet 2 values show up in a multi-select column. example: i have a multi-select column with multiple options and I want to count how many times "Drainage" and "Roadway" are both selected. This can be done with the filter in the grid using the "has all of" options. This is the formula I have currently.

=COUNTIF([Discipline In Court]:[Discipline In Court], FIND("Drainage/ Roadway", @cell) > 0)

Leibel S

@Justin Relitz

your formula should be:

=COUNTIF([Discipline In Court]:[Discipline In Court], AND(HAS(@cell, "Drainage"), HAS(@cell, "Roadway")))

Jen Lange

@Justin Relitz , what's the result of your current formula? I assume it's not achieving your objective?

Justin Relitz

@Jen Lange The result right now is its showing zero when it should be showing 1. No its not achieving the objective. I have a mutli-select column and one of the options has "Drainage" and "Roadway" in the column and it should show a count of one.

Leibel S

@Justin Relitz

your formula should be:

=COUNTIF([Discipline In Court]:[Discipline In Court], AND(HAS(@cell, "Drainage"), HAS(@cell, "Roadway")))

Justin Relitz

@Leibel S - this worked! Thanks so much for your help. This is a great way to use the HAS formula with the AND formula for multi-select columns.