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:

Answers

  • 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)))

  • GMichal
    GMichal ✭✭✭✭

    @Paul Newcome I was trying this same solution with my formula but obviously missing something:

    =COUNTIFS([Status]@row, OR(CONTAINS("Initial Testing",@cell), CONTAINS("Ready for Retesting"@cell)))

    I have a dropdown list in a column named "Status" where we track the progress in a process. The dropdown list contains the following choices:

    Initial Testing

    Ready for Review

    Ready for Retesting

    Ready for 2nd Review

    App Support Request Submitted

    Completed

    ON HOLD

    No Longer Needed

    My situation is that I'd like to count the number of times that "Initial Testing" and "Ready for Retesting" are chosen for each row from the dropdown in the Status column. Unfortunately, this testing can go thru multiple cycles (Initial Testing, then Ready for Review, then Ready for Retesting, then Ready for 2nd Review, then Ready for Retesting, etc.). I was trying something like this at first...

    =COUNTIF(Status@row, "Initial Testing") + COUNTIF(Status@row, "Ready for Retesting")

    but it only counts "1" when either of the testing selections is made; it does not total the number of times those testing selections are chosen.

    What am I missing?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @GMichal You would need to set up a column that has a unique identifier on every row. Then you would set up a copy row automation to copy the row to another sheet whenever the status changes to one of those two options. Finally you would use a COUNTIFS with cross sheet references to count how many times that unique identifier is on the other sheet.

  • GMichal
    GMichal ✭✭✭✭

    Thanks so much. I thought my syntax was off with a simple formula; not that I had taken the wrong approach.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!