Multiple users in dropdown formula

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?

Find more posts tagged with

Formulas

Accepted answers

All comments

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. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.