Count formula pulling from multi select

sjohanson

Help! I need a formula that will calculate the total # of times a department is listed within a multi select column.

Scenario:

1. Sheet "Project Plan" has a "Department(s)" column listing MEQA and Strategic Ops.
2. Sheet "Status Totals" where I want to build the formula

Paul Newcome

You will need a HAS function.

=COUNTIFS({Project Plan Department Column}, HAS(@cell, "MEQA"))

Paul Newcome

You will need a HAS function.

=COUNTIFS({Project Plan Department Column}, HAS(@cell, "MEQA"))

sjohanson

That worked thanks Paul! Follow up request. I would like the formula to tell me # of times the department is listed BUT - only for tasks that are still active (either in a: not started, in progress, in progress/at risk) category. See screen shot below. Is this possible?

Paul Newcome

Try something like this:

=COUNTIFS({Project Plan Department Column}, HAS(@cell, "MEQA"), "Project Plan Status Column}, @cell <> "Complete")

sjohanson

@Paul Newcome Unfortunately this didn't work. Any other suggestions?

Paul Newcome

Sorry about that. I fat fingered a quote that should have been a curly bracket:

=COUNTIFS({Project Plan Department Column}, HAS(@cell, "MEQA"), {Project Plan Status Column}, @cell <> "Complete")

sjohanson

THAT WORKED THANK YOU!

Paul Newcome

Happy to help. 👍️