count comma delimited list in excel

Posted on

QUESTION :

I have a column in Excel that contains a comma delimited list:

Header
A, B
A
A, B, C
D, A

I would like to extract this data so that I can count the occurrences of each item so that I would have the following results:

Count of Items
A    |    4
B    |    2
C    |    1
D    |    1

I am fine with hardcoding A, B, C, D in my list if necessary, but I have no clue what formula to use to count the items.

ANSWER :

If none of the items are prefixes or suffixes of other items (that is, nothing like “A, B”; “AA, BA”, etc.), then

=COUNTIF(range,"*"&RC[-1]&"*")

will work, where range is the source data (the 4 cells below Header in the example.)

Leave a Reply

Your email address will not be published. Required fields are marked *