Exclude values in a drop down list

I am using the formula below to exclude 2 values in a drop drown list and its not working.

Range 1 is looking at a sprint column (in this example sprint 7) on the source sheet

Range 2 is looking at a status column ( the two values i want to exclude is Pass and Dropped) on the same source sheet

Category at row value in this example is Sprint 7 on the metrics sheet i am creating - the Category column contains all the sprints on the metrics sheet

=COUNTIFS({FDM UAT Scenarios Range 1}, HAS(@cell, $Category@row, AND({FDM UAT Scenarios Range 2},(@cell <> "Pass", @cell <> "Dropped"))))

Find more posts tagged with

Accepted answers

KPH

Hi @Shonda

The COUNTIFS assumes the AND, you don't need to add that. You could enter the range and each criteria separately, or use an OR like this:

=COUNTIFS({FDM UAT Scenarios Range 1}, HAS(@cell, $Category@row), {FDM UAT Scenarios Range 2}, NOT(OR(HAS(@cell, "Pass"), HAS(@cell, "Dropped")))

Or is the second range isn't a multi-select:

=COUNTIFS([FDM UAT Scenarios Range 1]:[FDM UAT Scenarios Range 1], HAS(@cell, $Category@row), [FDM UAT Scenarios Range 2]:[FDM UAT Scenarios Range 2], NOT(OR((@cell = "Pass"), (@cell = "Drop"))))

All comments

KPH

Hi @Shonda

The COUNTIFS assumes the AND, you don't need to add that. You could enter the range and each criteria separately, or use an OR like this:

=COUNTIFS({FDM UAT Scenarios Range 1}, HAS(@cell, $Category@row), {FDM UAT Scenarios Range 2}, NOT(OR(HAS(@cell, "Pass"), HAS(@cell, "Dropped")))

Or is the second range isn't a multi-select:

=COUNTIFS([FDM UAT Scenarios Range 1]:[FDM UAT Scenarios Range 1], HAS(@cell, $Category@row), [FDM UAT Scenarios Range 2]:[FDM UAT Scenarios Range 2], NOT(OR((@cell = "Pass"), (@cell = "Drop"))))

Shonda

@KPH ... thank you for your help, the formula worked beautifully!

KPH

Great news! I was replying on my phone and struggled a bit so I'm especially pleased it worked!

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.