Multiple users in dropdown formula

Alisia Gill

I'm trying to countifs a user is on target. My sheet column is a multiple selection column, some rows have multiple users selected. When I create my formula, it doesn't count if said user is sharing a responsibility with someone else. Here is my current formula =COUNTIFS({2021-2022 COMPLIANCE WORK PLAN Person Responsible}, "JS", {2021-2022 COMPLIANCE WORK PLAN Status}, "On Target").

This user also shares a responsibility with someone else, she should show 5 on target, the formula only shows 4 since 1 tasks is shared with someone else.

Also, once I get this working how do I set it up on the dashboard so they can click on their name and show just their tasks?

Paul Newcome

Try this...

=COUNTIFS({2021-2022 COMPLIANCE WORK PLAN Person Responsible}, HAS(@cell, "JS"), {2021-2022 COMPLIANCE WORK PLAN Status}, "On Target")

AS for the dashboard question, is it a multi-select CONTACT type column or just a dropdown?

Alisia Gill

Thank you that worked.

The sheet is a Multi Select Dropdown

Paul Newcome

Since it is a dropdown as opposed to a contact type column, there is no easy way to set things up so they can just jump straight to their tasks. If it was set up as a contact type column, you could replicate the dashboard but have everything set up to filter by current user then provide a link to the "My Dashboard" on the main dashboard.

Alisia Gill

I changed the column to contact, and created the "View My Dashboard" and it worked perfectly. Thank you.

Paul Newcome

Happy to help. 👍️