Count multiple drop downs all containing a specific text within the same cell

Hi everybody,

I have a multiple drop-down column where I am referencing various Row IDs. They have an autonumber followed by a prefix.

As an example, a row can have a multiple choice filled with the following

001A ; 002A ; 005A ; 001B ; 003C ; 006C

I want to be able to count how many of the options are selected that have a specific prefix. Say I want to know that there are three choices with a prefix A (001A, 002A, and 005A in the example above).

How can I count that with a formula?

Thanks anybody outhere! :-)


Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ok. So what you will need is a series of helper columns (one for each suffix) that will house the count for each individually.

    =(LEN([Column Name]@row) - SUBSTITUTE([Column Name]@row, "SUFFIX", "")) / LEN("SUFFIX")

    Once you have your helper columns, you can do a basic SUM of each column to get the total number of entries for each suffix.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!