Help with COUNTIFS Formula for Multi-Select Dropdowns in Cross-Sheet Reference

Options

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!

Tags:

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/21/24
    Options

    @alamn

    =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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!