COUNTIF CONTAINS(OR(

kim.wade56346

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!

julie.gawle77496

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. 

Paul Newcome

Try something like this...

 

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

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

@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

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

Paul Newcome

Happy to help. 👍️