# Count number of unique initials within cells

1. ## 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.

2. ## 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>

3. ## 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. ## 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.

5. ## Re: Count number of unique initials within cells

The solution above may give wrong result. Try the attached

6. ## 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!

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