Using COUNTIFS with CONTAINS and OR

Hello,

I'm trying to count the number of times a given option in a multi-select field appears across several columns that utilize the same list of options. I've tried is several ways without success.


=COUNTIFS({Intake Data Range 11}, CONTAINS("CCAP", @cell), OR({Intake Data Range 12}, CONTAINS("CCAP", @cell))) returns #INVALD DATA TYPE

=COUNTIFS({Intake Data Range 11}, CONTAINS("CCAP", @cell), {Intake Data Range 12}, OR(CONTAINS("CCAP", @cell))) returns the number of times CCAP appears in range 12, but not range 11

=COUNTIFS({Intake Data Range 11}, {Intake Data Range 12}, OR(CONTAINS("CCAP", @cell))) returns #INVALID OPERATION


Ultimately, my goal will be to get a count of the number of time "CCAP" appears across five multi-select columns.

Thanks for your help!

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @bstormer

    Instead of using OR in your COUNTIFS, try adding 5 separate COUNTIFS together to return a total count, checking each individual cell in each column.

    Ex:

    =COUNTIFS(first column formula) + COUNTIFS(second column) + COUNTIFS(third) + COUNTIFS(fourth) + COUNTIFS(fifth)

    I would also suggest using the HAS Function instead of CONTAINS, since you're looking for a specific value in a multi-select column.

    Try:

    =COUNTIFS({Intake Data Range 11}, HAS(@cell, "CCAP")) + COUNTIFS({Intake Data Range 12}, HAS(@cell, "CCAP"))

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @bstormer

    Instead of using OR in your COUNTIFS, try adding 5 separate COUNTIFS together to return a total count, checking each individual cell in each column.

    Ex:

    =COUNTIFS(first column formula) + COUNTIFS(second column) + COUNTIFS(third) + COUNTIFS(fourth) + COUNTIFS(fifth)

    I would also suggest using the HAS Function instead of CONTAINS, since you're looking for a specific value in a multi-select column.

    Try:

    =COUNTIFS({Intake Data Range 11}, HAS(@cell, "CCAP")) + COUNTIFS({Intake Data Range 12}, HAS(@cell, "CCAP"))

    Cheers,

    Genevieve