What formulas should I use to count two criteria (including from a multiple dropdown option)?

I want to count "Enrolled" from "Case Status" AND "Medicaid" from "Insurance Type." I used the COUNTIF formula, but it seems like Smartsheet isn't counting the Medicaid cell if it's within a cell with multiple criterias selected. Screenshots below:

You can see that even though 2 fit the criteria, the sheet summary only accounts for 1 as the response.

I tried the below formula:

=COUNTM(COUNTIFS([Case Status]:[Case Status], CONTAINS("Enrolled", @cell), [Insurance Type]:[Insurance Type], CONTAINS("Medicaid", @cell)))

Thanks!

Find more posts tagged with

Healthcare

Formulas

Accepted answers

Mark Safran

Hey @pothosplant

Instead of using the CONTAINS( ) function, try using HAS( ) instead. This does an exact-match lookup and was designed to specifically handle multi-select dropdown and contact columns. Your formula would be:

=COUNTIFS([Case Status]:[Case Status], "Enrolled", [Insurance Type]:[Insurance Type], HAS(@cell, "Medicaid"))

-MCS

All comments

Mark Safran

Hey @pothosplant

=COUNTIFS([Case Status]:[Case Status], "Enrolled", [Insurance Type]:[Insurance Type], HAS(@cell, "Medicaid"))

-MCS

pothosplant

@Mark Safran thanks!! It worked!

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.