Count Unique SME names

Art Schneiderheinze

Hi. I have two columns SME #1 (Name) and SME #2 (Name) with names of subject-matter experts from rows 5 to 155. In many cases, one SME is assigned to more than one course (one course=1 row).

I'd like to count the number of unique names in both columns.

                      SME #1 (Name)     | SME #2 (Name)

Course 1    |   Donald Duck          | 

Course 2    |   Mickey Mouse        |  Fit-it Felix

Course 3    |   Donald Duck          |  Mickey Mouse

 

There is only one SME for Course 1, while there are two for Course 2. There are two for Course 3, but both were already counted in Course 1 and 2.

The cell with the COUNT formula should display 3, since there are three unique names in both columns (Donald Duck, Michey Mouse, and Fix-it Felix). It should not count BLANK cells.

What is a good way to do this?

Paul Newcome

The simplest way I have found is to create a helper column for each SME column. I will go ahead and assume that "Course 1" is on row 1 and that you only have the 2 SME columns for this example. This setup is expandable though.

 

In row 1 of the [SME 1 Helper] column enter

=COUNTIFS($[SME #1 (Name)]$1:$[SME #2 (Name)]@row, @cell = [SME #1 (Name)]@row, $[SME #1 (Name)]$1:$[SME #2 (Name)]@row, ISTEXT(@cell))

 

You can then drag fill this over to the [SME 2 Helper] column and drag fill on down the line. 

 

What this will do is provide a 1 for the first instance of each name, 2 for the second, 3 for the third, etc. and a 0 for blank cells. You can then use a basic

 

=SUMIFS([SME 1 Helper]:[SME 2 Helper], [SME 1 Helper]:[SME 2 Helper], @cell = 1)

 

to add up all of the 1's which would give you the count of unique entries covering both columns. See the screenshot below for an example of what this would look like.