Formula to count of items in a multi dropdown list

kim.wade56346

What would the formula look like if you were checking a multi-select column to see if it contains two options?

Paul Newcome

Please reference the last post in the thread. That should be it.

ademoss

Thank you! That one counts ALL the cells that have either of those values, I only want it to count if it has BOTH in the same cell. Does that make sense?

Also did not know that varied by country, very cool!

Thanks!

Paul Newcome

Try something like this...

 

=COUNTIFS([Column Name]:[Column Name], AND(CONTAINS("Option 1", @cell), CONTAINS("Option 2", @cell)))

Andrée Starå

Happy to help!

I saw that Paul answered already!

Let me know if I can help with anything else!

Have a fantastic weekend!

Best,

Andrée

Soumya Gupta

I want the same functionality but for if and not countif.

So if someone selects one value from the drop down, it should reflect as 1 in the next column.

Similarly if 2 values are selected then, 2 and so on..

As can be seen in the image below, if two options in impacted pillar column are checked impacted points should be 2.

 

Paul Newcome

Try...

 

=COUNTM([Impacted Pillar]@row)

Soumya Gupta

It worked! I did't know something like COUNTM exists!!

Thank you

Paul Newcome

Happy to help. It has only been around for about a month. Previously you would have needed to adapt one of the solutions using the LEN and CHAR(10) functions.

alcapps

I am trying to write a formula that will answer Yes if a request has been approved but only if "Patient Report" is selected in a multiselect field. If "Patient Report" is not selected, there is no need for approval. This is part of a larger formula with several other fields involved that answers Yes if all criteria has been met. When I add this piece (does not contain), the formula does not work anymore.

If [multiselect cell] contains "specific text" and [approval cell] is not blank or [multiselect cell] does not contain "specific text" then yes otherwise no?

I've tried this but it doesn't seem to be working...

=IF(OR(AND(CONTAINS("Patient Report", [Systems Affected]),[Medical Director Approval]@row <>""), NOT(CONTAINS("Patient Report",[Systems Affected]@row))), "Yes", "No")

Paul Newcome

I'm not sure I follow.

 

Your formula says to populate "Yes" if

 

[Systems Affected] contains "Patient Report" and [Medical Director] is not blank

or

[Systems Affected] does not contain "Patient Report"

.

If you have confirmed this is how you want the formula to work, then the only issue would be a row reference in your first CONTAINS function.

 

You have:

=IF(................CONTAINS("Patient Report", [Systems Affected])...................)

but it should be 

=IF(................CONTAINS("Patient Report", [Systems Affected]@row)...................)

alcapps

Wow! That's what it was.  

Paul Newcome

Happy to help! 

Jennifer Lenander

I have a column that has about 20 dropdown options and multiple options can be selected. On a separate sheet I have all 20 options listed and I want to know how many times each specific option was selected in my main sheet. I have tried various formulas and nothing is working. Does anyone have any ideas?

=COUNTIF({Range 1}, [Specific Claim]1)

=COUNTIFS({Range 1}, CONTAINS([Specific Claim]1, {Range 1}))

Genevieve P.

Hi @Jennifer Lenander

Try using Paul's formula at the top of this thread:

=COUNTFS({Range 1}, CONTAINS([Specific Claim]1, @cell))

Since it's a multi-dropdown column, you may want to try the HAS function instead:

=COUNTF({Range 1}, HAS(@cell, [Specific Claim]@row))