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?

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, *"))

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

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

Please Login or Register  to view this content.