+ Reply to Thread
Results 1 to 14 of 14

Count occurrences of comma separated text from a range

  1. #1
    Registered User
    Join Date
    03-25-2015
    Location
    Minnesota
    MS-Off Ver
    11
    Posts
    7

    Count occurrences of comma separated text from a range

    Column A contains the possible items that could be in column B, but may be repeating.
    Column B contains the unique text from column A. It may contain between 1-3 unique items separated by a comma.
    I'm trying to figure out a formula for column C that will count and total the number of occurrences of each item in column B based on the number of times they occur in Column A. It's easy if there is only one item to find from column B, but I'm not sure what to do if there is more than one item separated by a comma.

    Thanks. I appreciate the help.

    Screen Shot 2015-09-16 at 11.16.58 AM.png

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count occurrences of comma separated text from a range

    Many members are unable to see images in the *.png format.

    If you need to post an image post it in the *.jpg format.

    Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,602

    Re: Count occurrences of comma separated text from a range

    As this is your first post, you may not realise that the Forum software is incompatible with some browsers, which prevents many contributors from being able to view PNG files - it is better to attach a sample Excel workbook instead.

    Pete

  4. #4
    Registered User
    Join Date
    03-25-2015
    Location
    Minnesota
    MS-Off Ver
    11
    Posts
    7

    Re: Count occurrences of comma separated text from a range

    I've attached the sample file. ThanksTest.xlsx

  5. #5
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Count occurrences of comma separated text from a range

    Hi.

    In C1:

    =SUMPRODUCT(0+ISNUMBER(FIND(","&A$1:A$14&",",","&B1&",")))

    Copy down as required.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  6. #6
    Registered User
    Join Date
    03-25-2015
    Location
    Minnesota
    MS-Off Ver
    11
    Posts
    7

    Re: Count occurrences of comma separated text from a range

    Quote Originally Posted by XOR LX View Post
    Hi.

    In C1:

    =SUMPRODUCT(0+ISNUMBER(FIND(","&A$1:A$14&",",","&B1&",")))

    Copy down as required.

    Regards
    That works beautifully. Thanks. Can you easily explain how it works?

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Count occurrences of comma separated text from a range

    Quote Originally Posted by rra1968 View Post
    That works beautifully. Thanks. Can you easily explain how it works?
    Sure. For the formula in C3, for example, we simply count how many times each of the entries in the range A1:A14 occurs within the entry in B3.

    We might do this by a simpler formula than that which I posted, viz:

    =SUMPRODUCT(0+ISNUMBER(FIND(A$1:A$14,B1)))

    and indeed, given the data you provided, this would also give correct results.

    However, I assumed that you were using dummy data and that, in reality, your entries were not as simple as single letters of the alphabet. As such, it is perferable to employ a more rigorous version of the formula, as I gave. The reason being that, if, for example, B3 contained, not "D,E,F", but "DA, E, F", then the formula:

    =SUMPRODUCT(0+ISNUMBER(FIND(A$1:A$14,B3)))

    would incorrectly return 11, since the "A"s in column A are being considered as being part of this entry.

    By first adding a comma before and after each of the entries in A1:A14 - and also to that in B3 - we ensure that the correct result is returned, since, now, ",A," is not found within ",DA,E,F,".

    Regards

  8. #8
    Registered User
    Join Date
    03-25-2015
    Location
    Minnesota
    MS-Off Ver
    11
    Posts
    7

    Re: Count occurrences of comma separated text from a range

    Quote Originally Posted by XOR LX View Post
    Sure. For the formula in C3, for example, we simply count how many times each of the entries in the range A1:A14 occurs within the entry in B3.

    We might do this by a simpler formula than that which I posted, viz:

    =SUMPRODUCT(0+ISNUMBER(FIND(A$1:A$14,B1)))

    and indeed, given the data you provided, this would also give correct results.

    However, I assumed that you were using dummy data and that, in reality, your entries were not as simple as single letters of the alphabet. As such, it is perferable to employ a more rigorous version of the formula, as I gave. The reason being that, if, for example, B3 contained, not "D,E,F", but "DA, E, F", then the formula:

    =SUMPRODUCT(0+ISNUMBER(FIND(A$1:A$14,B3)))

    would incorrectly return 11, since the "A"s in column A are being considered as being part of this entry.

    By first adding a comma before and after each of the entries in A1:A14 - and also to that in B3 - we ensure that the correct result is returned, since, now, ",A," is not found within ",DA,E,F,".

    Regards
    Yes, the data used was an example. In reality they would be people's names. The other glitch, is that sometimes there are words in column B that are not contained in the column A range. I've attached another example with names this time.Attachment 419559
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    03-25-2015
    Location
    Minnesota
    MS-Off Ver
    11
    Posts
    7

    Re: Count occurrences of comma separated text from a range

    Now for some reason the two examples you gave, even with the ","

  10. #10
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Count occurrences of comma separated text from a range

    Sorry, but I can't seem to see your attempt at adapting my formula in this file?

    Regards

  11. #11
    Registered User
    Join Date
    03-25-2015
    Location
    Minnesota
    MS-Off Ver
    11
    Posts
    7

    Re: Count occurrences of comma separated text from a range

    Sorry...I've attached it. I'm not sure I understood your second example...
    Attached Files Attached Files

  12. #12
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Count occurrences of comma separated text from a range

    But of course. In your original example the entries were separated by "," (comma only). In this new example they are separated by ", " (comma followed by a space). You need to be consistent!

    For your new workbook, replace all instances of:

    ","

    with:

    ", "

    Regards

  13. #13
    Registered User
    Join Date
    03-25-2015
    Location
    Minnesota
    MS-Off Ver
    11
    Posts
    7

    Re: Count occurrences of comma separated text from a range

    XOR LX,
    Thanks for your help. That did exactly what I wanted.

  14. #14
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Count occurrences of comma separated text from a range

    Sure!

    Cheers

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to count values separated by a comma
    By PlamSa in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 11-19-2013, 09:10 AM
  2. [SOLVED] Counting number of occurrences in cells with numbers separated by a comma
    By Losguapos1 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-02-2013, 09:44 AM
  3. [SOLVED] Count occurrences of text in a range
    By braydon16 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-18-2013, 08:33 PM
  4. Count number of occurrences separated by comma
    By Niclal in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-23-2013, 03:39 AM
  5. count of comma separated values
    By bujji1305 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-01-2012, 07:33 PM
  6. [SOLVED] Need Help: how to count occurrences of a text character in a range
    By leemanns in forum Excel General
    Replies: 7
    Last Post: 05-05-2012, 01:51 PM
  7. count comma separated information
    By fjosef87 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 04-04-2010, 10:47 AM
  8. Replies: 0
    Last Post: 08-08-2005, 01:05 PM

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