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.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!