+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    05-17-2012
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Exclamation 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?
    Last edited by priya.excel; 05-17-2012 at 09:13 PM. Reason: More details were needed in the problem

  2. #2
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,452

    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, *"))
    Last edited by Cutter; 05-17-2012 at 07:27 PM.

  3. #3
    Registered User
    Join Date
    05-17-2012
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2010
    Posts
    5

    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. #4
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,199

    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
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

  5. #5
    Registered User
    Join Date
    05-17-2012
    Location
    San Francisco, CA
    MS-Off Ver
    Excel 2010
    Posts
    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!


    Quote Originally Posted by Haseeb A View Post
    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. #6
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,199

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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