Using COUNTIFS with CONTAINS and OR

bstormer

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!

Genevieve P.

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

Genevieve P.

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