Count number of unique initials within cells

[Keithflc]

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

[dilipandey]

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>

[Keithflc]

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?

[e.isayev]

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.

[e.isayev]

The solution above may give wrong result. Try the attached

[Keithflc]

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!