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

Anes

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

Anes

Paul Newcome

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.

Paul Newcome

You would use something along the lines of

=COUNTIFS([Column Name]:[Column Name], CONTAINS("A", @cell))

Anes

Hi Paul,

Thanks for the response. :-)

Unfortunately, this one does not pick up how many of the various multiple choices are there.

So even if my multiple choice cell contains 001A, 002A, and 005A, he only counts it as 1, not 3. It basically goes and counts if there is at least one "A", and I need it count how many "A"s it can find.

Thanks again! :-)

Paul Newcome

How many different suffixes are there? Obviously "A". Any others?

Anes

Well, I oversimplified the question here. I have several unique suffixes, with a chance that they will grow to a few more (say 10 total):

ANL

KRS

FRG

TOS

CXM

CLG

Paul Newcome

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.

Anes

Hey Paul,

Great, works like a charm! :-) Thanks!

Paul Newcome

Happy to help. 👍️