Using COUNTIF on a multi-select column.

I am trying to use COUNTIF on a multiselect column when two or more items are selected.

very easy when only one item is selected but not working with multiple items selected.

Answers

  • Rodolfo Garcia
    Rodolfo Garcia ✭✭✭✭

    @FernandoO Could you share , how are using the formula and what you get, and also what are you using instead and what you want to get.


    Thanks

    Rodolfo

  • I have tried

    =COUNTIF(AND({Range1},"Criteria1",{Range1},"Criteria2")) and get an Incorrect argument.

    I have tried using Contains as well.

    A little more context

    I have a multi- select column and I want to count all the instances of

    A -only selected

    B only selected

    A&B selected selected. (Not to include A-only instances or B-only instances

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @FernandoO

    The way I would do this is to set up a multi-select column in the sheet with the formula. Then I would reference the cell in that multi-select column as my "criteria" in the formula, like so:

    =COUNTIF({Range1}, HAS(@cell, [Multi Select]@row))

    If you only have two possible options to select, you could also look for that exact combination by removing the HAS, like so:

    =COUNTIF({Range1}, [Multi Select]@row)

    Let me know if this makes sense and works for you.

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!