Use of countifs when multiple dropdown values are in cells
I'm attempting to count a range of cells (columns) where the value may have one or more values included in the cell from a dropdown value.
Example: Workstream may have "Architecture" "testing" "UAT" included in the cell in one instance and in others only "Architecture". My formula seems to be counting only if "Architecture" alone is in the cells. My formula also looks for % complete less than 100% and start and finish dates in the past.
=COUNTIFS(Workstream:Workstream, "Architecture", [% Complete]:[% Complete], <1, Status:Status, <>"withdrawn", Start:Start, <TODAY(), Finish:Finish, <TODAY())
How can I change the formula to work instead of looking for exact "Architecture" to more of Contains Architecture?
Best Answer
-
With a multi-select column, you'll want to add in the HAS function to see if the cell has this value (along with other values).
Try this:
=COUNTIFS(Workstream:Workstream, HAS(@cell, "Architecture"), [% Complete]:[% Complete], <1, Status:Status, <>"withdrawn", Start:Start, <TODAY(), Finish:Finish, <TODAY())
Let me know if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
With a multi-select column, you'll want to add in the HAS function to see if the cell has this value (along with other values).
Try this:
=COUNTIFS(Workstream:Workstream, HAS(@cell, "Architecture"), [% Complete]:[% Complete], <1, Status:Status, <>"withdrawn", Start:Start, <TODAY(), Finish:Finish, <TODAY())
Let me know if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Genevieve P Rocks!!! Thank you so much! Spent a few hours trying to get that figured out. It worked!
-
@Ed Olszanowski So glad to hear it worked for you! 🙂
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!