#UNPARSEABLE using COUNTIF for text objects

Steven Switz

I have a Form which allows the recipient to answer a question via a drop down list:

Example: Where would you like to go for vacation?

List includes: U.S., Europe, Asia, Australia

It's a pick many list so users can select any number of answers. The resulting cell then may contain something like: U.S. Europe Asia

I would like to create a COUNTIF for each of the possible contents so the end result looks like:

[RESULTS] [COUNT]

U.S. 7

Europe 16

Asia 24

Australia 8

My formula looks like this:

=COUNTIF([Destination], CONTAINS(U.S.)

What am I missing here?

Mark Cronk

Try:

=COUNTIF([Destination]:[destination], HAS(@cell, "U.S."))

HAS looks for an exact match in multiselect cells.

https://help.smartsheet.com/function/has

Mark

Mark Cronk

Try:

=COUNTIF([Destination]:[destination], CONTAINS("U.S.",@cell))

Mark

Steven Switz

https://community.smartsheet.com/discussion/comment/283519#Comment_283519

That did solve the problem but now I have another issue: The list is a little longer than what I used for the example and now I'm having data quality issues. For example, with "U.S." and "U.S. Virgin Islands" in the pick list, the provided formula is counting both options seemingly because they both contain "U.S.". Do you know how I can make it count only exactly what's in the quotes?

Mark Cronk

Try:

=COUNTIF([Destination]:[destination], HAS(@cell, "U.S."))

HAS looks for an exact match in multiselect cells.

https://help.smartsheet.com/function/has

Mark

Steven Switz

Well done, @Mark Cronk! You are a SmartSheet wizard

Mark Cronk

Glad you found a solution.  Thank you for contributing to the Community.

Mark