Formula to count of items in a multi dropdown list

kim.wade56346 · 11/07/19

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

Paul Newcome · 11/07/19

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

ademoss · 11/07/19

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 · 11/07/19

Try something like this...

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

Andrée Starå · 11/08/19

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 · 11/11/19

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 · 11/11/19

Try...

=COUNTM([Impacted Pillar]@row)

Soumya Gupta · 11/11/19

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

Thank you

Paul Newcome · 11/12/19

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 · 11/26/19

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 · 11/26/19

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 · 11/26/19

Wow! That's what it was.

Paul Newcome · 11/26/19

Happy to help!

Jennifer Lenander · 07/07/20

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. · 07/08/20

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

Formula to count of items in a multi dropdown list

Answers

Help Article Resources

Categories