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
- Smartsheet Customer Resources
- 62.5K Get Help
- 367 Global Discussions
- 202 Industry Talk
- 432 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 447 Show & Tell
- 29 Member Spotlight
- 1 SmartStories
- 286 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!