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

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)

Find more posts tagged with

Formulas

Reports

Accepted answers

Leibel S

@Justin Relitz

your formula should be:

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

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.