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

pothosplant

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!

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

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

pothosplant

@Mark Safran thanks!! It worked!