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"))))
Best Answer
-
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"))))
Answers
-
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 ... thank you for your help, the formula worked beautifully!
-
Great news! I was replying on my phone and struggled a bit so I'm especially pleased it worked!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!