count comma delimited list in excel

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.

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. — blm, Jan 18, 2016 at 18:00
I was simplifying the example so there shouldn't be any prefixes like that. Go ahead and answer and I'll mark it. — Dismissile, Jan 18, 2016 at 18:06

blm · Accepted Answer · 2016-01-18 18:10:28Z

1

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.)

answered Jan 18, 2016 at 18:10

blm

6201 gold badge5 silver badges13 bronze badges

Add a comment |

1 Answer 1