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! :-)
Anes
Best 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.
Answers
-
You would use something along the lines of
=COUNTIFS([Column Name]:[Column Name], CONTAINS("A", @cell))
-
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! :-)
-
How many different suffixes are there? Obviously "A". Any others?
-
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
-
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.
-
Hey Paul,
Great, works like a charm! :-) Thanks!
-
Help Article Resources
Categories
Check out the Formula Handbook template!