#UNPARSEABLE using COUNTIF for text objects

03/15/21
Accepted

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?

Best Answer

  • Mark CronkMark Cronk ✭✭✭✭✭
    edited 03/15/21 Accepted Answer

    Try:

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

    HAS looks for an exact match in multiselect cells.

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark CronkMark Cronk ✭✭✭✭✭
    edited 03/15/21

    Try:

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • 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 CronkMark Cronk ✭✭✭✭✭
    edited 03/15/21 Accepted Answer

    Try:

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

    HAS looks for an exact match in multiselect cells.

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

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

  • Mark CronkMark Cronk ✭✭✭✭✭

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

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Sign In or Register to comment.