How to do Countifs when drop down allows multiple select

Mar_star

Hello,

I am trying to set up a formula that would count a contract number that is in a cell that allows multiple drop downs. I have looked up "Has" and "Contains" and I am pretty sure I am just following the formula wrong?

Can anyone help?

Kelly Moore

Hey @Mar_star

If you're wanting to 'hard code' your contract number "H1111" into your formula, you would use

=COUNTIFS({MY 2025 Action Plan Range 3}, HAS(@cell, "H1111"))

If, however, you have a list where every row has a different contract number that you wanted counted then

=COUNTIFS({MY 2025 Action Plan Range 3}, HAS(@cell,[Contract Number]@row))

you would edit the formula above and change the [Contract Number] name to the exact name of your column. You would leave the @row .

Does either of these work for you?
Kelly

Mar_star

Thank you Kelly! The first one worked, I appreciate you!

=COUNTIFS({MY 2025 Action Plan Range 3}, HAS(@cell, "H1111"))

Mar_star

Hello @Kelly Moore could you help me with this one?

I am trying to add another condition to this formula and its bring back a 0. What am I doing wrong here?

=COUNTIFS({MY25 CTM Detail Report Range 7}, HAS(@cell, "H1111", {MY25 CTM Detail Report Range 5}, "Enrollment/Disenrollment"))

Kelly Moore

Hey @Mar_star

You need to close your HAS function. I’m surprised the formula did not result in an UNPARSEABLE.

Try this:

=COUNTIFS({MY25 CTM Detail Report Range 7}, HAS(@cell, "H1111"), {MY25 CTM Detail Report Range 5}, "Enrollment/Disenrollment")

Will this work for you?

Kelly