Help with COUNTIFS Formula for Multi-Select Dropdowns in Cross-Sheet Reference
Hello Smartsheet Community,
I am currently working on a formula that needs to reside in a metadata sheet and am facing a challenge with multi-select dropdown columns. Specifically, I need to count rows from another sheet based on the following criteria:
- RAID Type is "Risk"
- Impact Type contains the substring "Business" (this is a multi-select dropdown)
- RAID Status is "Open"
The difficulty arises with the "Impact Type" column, where "Business" could be one of several selected options in the multi-select dropdown. I need the formula to count any entries that contain "Business" among their selected options.
Here's the current approach, which isn't suitable for multi-select:
=COUNTIFS({RAID Type}, "Risk", {Impact Type}, "Business", {RAID Status}, "Open")
This doesn't work because "Impact Type" needs to check for "Business" as part of a list of selected values, not as a single value.
Does anyone have experience or suggestions on how to structure this formula to handle multi-select dropdowns effectively? Any help or guidance would be appreciated as I am trying to automate a reporting process and need accurate counts based on these criteria.
Thank you in advance for your help and suggestions!
Answers
-
=COUNTIFS({RAID Type}, "Risk", {Impact Type}, Contains("Business",@cell), {RAID Status}, "Open")
If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 438 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 282 Events
- 32 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!