# Counting instances of each item in column 1, where each cell in column 1 is a list of item

1. ## Counting instances of each item in column 1, where each cell in column 1 is a list of item

Apologies.. my actual problem is more complex than what I posted originally.. Making edits in this post to reflect that

If I have a list of delimiter-separated group of items in one column and a frequency of that list in second column , how can I use excel to get a count of each of the individual items.

I know that this isn't self-explanatory. So here is an example:

Given:

Column A (list) Column B (Frequency of this list)
apple, banana, apple 2
apple, orange, banana, banana 4
orange 3
pineapple, orange, banana, apple 1

I need a result like this:
Item Count of item
apple 9
banana 11
orange 8
pineapple 1

Can anyone help me with this?

2. ## Re: Counting instances of each item in column 1, where each cell in column 1 is a list of

Can you do Text to Columns?

If so do a COUNTIF() on the entire resulting range.

Otherwise something like this would be needed for apple:

=SUM(COUNTIF(A:A,"apple,*"),COUNTIF(A:A,"* apple"),COUNTIF(A:A,"* apple, *"))

3. ## Re: Counting instances of each item in column 1, where each cell in column 1 is a list of

Thanks Cutter for your reply. Your answer will resolve my original problem, but I realized that the actual problem that I need to solve is bit more complex.

4. ## Re: Counting instances of each item in column 1, where each cell in column 1 is a list of

Hello Priya,

Consider these:

Data: A1:A4

A6 to down, names like apple, banana etc..

So, try this in B6, with CTRL+SHIFT+ENTER, rather than just ENTER, then copy down.

``Please Login or Register  to view this content.``
Assume your data always be in same format. like ", " after each item

5. ## Re: Counting instances of each item in column 1, where each cell in column 1 is a list of

Hi Haseeb,
Thanks so much! This works like a charm. Exactly what I needed!

Originally Posted by Haseeb A
Hello Priya,

Consider these:

Data: A1:A4

A6 to down, names like apple, banana etc..

So, try this in B6, with CTRL+SHIFT+ENTER, rather than just ENTER, then copy down.

``Please Login or Register  to view this content.``
Assume your data always be in same format. like ", " after each item

6. ## Re: Counting instances of each item in column 1, where each cell in column 1 is a list of

Sine you have 'space' in between items, better to use PROPER.

``Please Login or Register  to view this content.``

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1