COUNTIF CONTAINS(OR(

kim.wade56346
kim.wade56346 ✭✭
edited 12/09/19 in Formulas and Functions

Can anyone help me with a formula for a multi-select column where I am counting the number of occurrences of two of the options?  I think it would be a COUNTIFS(CONTAINS(OR( formula, but can't seem to get it to work.

Thanks!

Tags:

Comments

  • Kim - I'm thinking you might need to use the HAS function. That's new with the multi-select column feature. Maybe...there isn't alot of information out there about how to count this new column type. Or maybe COUNTM? Not totally sure your unique situation. 

    However, I have a COUNTIFS situation with a multi-select column that I can't figure out. I have a sheet with a list of project impacts and the corresponding audience that is impacted by this change. I have an intra-sheet formula that determines if something is entered in the Impact? column and returns a Y or N. I want to write a formula using the multi-select column that answers the question: 

    "How many times in this specific audience impacted?"

    And I want it to count the number of times an audience is selected AND there is a Y in my Impact column. I hope the screen shots help. 

    I'm thinking this needs to be some kind of nested formula with COUNTIFS and HAS, but I cannot figure it out. 

    Multi-select screen shots.jpg

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try something like this...

     

    =COUNTIFS([Multi-Select Column]:[Multi-Select Column], OR(CONTAINS("option a", @cell), CONTAINS("option b", @cell)))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!