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