Exclude values in a drop down list

Shonda

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"))))

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"))))

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!