+ Reply to Thread
Results 1 to 5 of 5

Count number of unique initials within cells

  1. #1
    Registered User
    Join Date
    01-17-2013
    Location
    Uk
    MS-Off Ver
    Excel 2007
    Posts
    5

    Count number of unique initials within cells

    Hi all,

    I've searched long and hard for the solution to this one.

    I have a spreadsheet where I input what volunteers attend each session.

    So the cell that contains volunteers looks like this: Ra Ry Ch

    I'm looking to be able to count up the number of unique volunteers attended sessions of a period of time.

    I've tried a formula like: =SUMPRODUCT((D4:D41<>"")/COUNTIF(D4:D41,D4:D41&"")) However this only counts the number of unique entries for cells as a whole, not the individual initials within the cells.

    For example if the cells look like this:

    RE TY FT
    TY RE BE IL
    RE TY FT
    IL TY
    IL TY

    The above formula will return 3, however I would like it to return 5 as this is the number of unique volunteers to have attended sessions for this time period. Hopefully this makes sense, I've attached an example workbook.Example.xlsx

    I understand that there may not be a solution to the way I've set up the data, so if there is another way to count volunteers let me know.

    Thanks for your time

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Count number of unique initials within cells

    Hi Keith,

    Any possibility for you to have initials in different cells rather than having all in one cell as that would be easy to manage and do calculations..

    Regards,
    DILIPandey
    <click on below * if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Registered User
    Join Date
    01-17-2013
    Location
    Uk
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Count number of unique initials within cells

    Thanks DILIPandey,

    I thought about doing this, however, there are hundreds of sessions a year and up to 6 volunteers per session, so it would end up taking up a massive sheet. I assume this would be the only option then?

  4. #4
    Registered User
    Join Date
    10-02-2012
    Location
    Baku
    MS-Off Ver
    Excel 2010
    Posts
    273

    Re: Count number of unique initials within cells

    Hi,
    I am not sure wether my solution will satisfy you or not.

    =SUM(IFERROR(IF(FIND($E$1:$E$8,C2)>0,1),0)) this is array formula
    but to know the number of unique volunteers you should list their names for once and then formula will calculate volunteers by using that list.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-02-2012
    Location
    Baku
    MS-Off Ver
    Excel 2010
    Posts
    273

    Re: Count number of unique initials within cells

    The solution above may give wrong result. Try the attached
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-17-2013
    Location
    Uk
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Count number of unique initials within cells

    Thanks for your help eisayew. It returns the result I was looking for, however I wanted to avoid having to list the volunteers names.

    I think this will be the closest I get to what I'm looking for. Thanks again!

+ 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