Formulas with Drop-down Multi-Select Column

Cary_2207
Cary_2207 ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

Hi there!

I have been using the new functionality of the "Drop-down Multi-select" column and I am trying to capture all of the different combinations that can be selected within the column.

The column is supposed to capture the root cause of an issue and the person working on the issue is able to select multiple root causes.

I need to COUNT the amount of issues that have a specific root cause selected and started with the formula below. The issue here is that this ONLY works if ONE of the causes is selected not multiple.

=COUNTIF([Root Cause]:[Root Cause], "Callidus") - On the current sheet I created a summary field for each individual value and then ran a summary report to bring them all in. Then created a chart on a dashboard pointing to this summary report. There are gaps in the data since not capturing the combo values - only individual ones.

I would like some help creating a formula that takes into account all of the different combinations that may be selected in the column so it can COUNT those combos as it's own distinct value so I can report on it.

I created a sample sheet with the multi-select drop-down and populated it with the values currently in the real sheet - if anyone needs access please let me know and I will share this.

Thank you so much!

Cary

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Check out the CONTAINS function. If you use this as your criteria, it will search for that particular text string and flag a true value if it is found.

     

    =COUNTIFS([Root Cause]:[Root Cause], CONTAINS("Callidus", @cell))

    thinkspi.com

  • How do you count the row if ANY multi-select is shown? I want to count the rows that have data selected in the multi-select column.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Amy Evans

    The COUNT formula is created to count non-blank cells. Try:

    =COUNT([Column Name]:[Column Name])

    If you're using a COUNTIFS with other criteria, you can use <> "" as your criteria, meaning "not blank"

    =COUNTIFS([Column One]:[Column One], "Value", [Column Two]:[Column Two], <> "")

    Cheers,

    Genevieve