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)))


Best Answer

  • Mark Safran
    Mark Safran ✭✭✭✭✭
    Answer ✓

    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"))



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!