CountIf for a Multi-Select List

Options

Good Afternoon,

I am building out an executive dashboard to support our SME Team and I am looking to count the number of times a specific value appears in a multi-select column. I am getting Unparsable errors with every attempt, any suggestion would be helpful.

This is my formula - =COUNTIFS(SME - Feature Progress Range 3,CONTAINS("Asset TL", @cell)))



Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hello @SherieC

    It appears you have a cross sheet reference however it is not bracketed properly. Did you type the reference in or did you insert it from the Reference Another Sheet link? If you inserted it properly, the curly brackets should be present.

     =COUNTIFS({SME - Feature Progress Range 3},CONTAINS("Asset TL", @cell))

    If you didn't use the Insert Reference link, you will need to do so.


    Also, in your screenshot above, the colored parentheses are indicating you have an extra parenthesis at the end of the formula. Note the last parenthesis is black, not blue. An ending blue parenthesis typically indicates you have the correct number of parentheses in the formula.

    You mentioned that you have a multi select column, however, I only see single select answers present. Single select and multi select refer to the number of responses possible in a single cell. If you have a list of possible responses in a dropdown list but only allow one response per cell, that is a single select dropdown - regardless of how many choices you had to choose from. Is this what you meant?

    If your column is single select you wouldn't need the contains function

    COUNTIFS({SME - Feature Progress Range 3}, @cell="Asset TL")

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓
    Options

    Hello @SherieC

    It appears you have a cross sheet reference however it is not bracketed properly. Did you type the reference in or did you insert it from the Reference Another Sheet link? If you inserted it properly, the curly brackets should be present.

     =COUNTIFS({SME - Feature Progress Range 3},CONTAINS("Asset TL", @cell))

    If you didn't use the Insert Reference link, you will need to do so.


    Also, in your screenshot above, the colored parentheses are indicating you have an extra parenthesis at the end of the formula. Note the last parenthesis is black, not blue. An ending blue parenthesis typically indicates you have the correct number of parentheses in the formula.

    You mentioned that you have a multi select column, however, I only see single select answers present. Single select and multi select refer to the number of responses possible in a single cell. If you have a list of possible responses in a dropdown list but only allow one response per cell, that is a single select dropdown - regardless of how many choices you had to choose from. Is this what you meant?

    If your column is single select you wouldn't need the contains function

    COUNTIFS({SME - Feature Progress Range 3}, @cell="Asset TL")

    Kelly

  • SherieC
    SherieC
    edited 01/02/22
    Options

    Hi Kelly - It was a bracketing issue, thank you for the help on this one!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!