Using COUNTIFS with CONTAINS and OR

bstormer
bstormer ✭✭
in Formulas and Functions

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!

· Share on FacebookShare on Twitter

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

    · Share on FacebookShare on Twitter

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

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!