Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Count Unique Names in Excel (6 Simple Methods)

While working with large datasets we often may need to count unique and distinct values in Excel. Excel does not have any built-in function to count unique values or text. But, there are many techniques and approaches by which we can count these distinct values. Today in this article, we will demonstrate some methods to count unique names in Excel.


Overview

In the following image, you will get an overview of the whole article.

Overview Image of Counting Unique Names


Download Practice Workbook

Download this practice sheet to practice while you are reading this article.


6 Easy Methods to Count Unique Names in Excel

In the following, I have explained 6 simple and easy methods to count unique names in Excel.


1. Using SUMPRODUCT Function to Count Unique Names

The simplest and easiest way to count unique names in Excel is by using the SUMPRODUCT function. Using this function we can count unique values in two ways. Let’s learn these ways.


1.1. SUMPRODUCT with COUNTIF

In the following situation, we are given a dataset where some Employees’ names and their Gross Salary are given. Now we have Employees whose names appeared more than once. So we have to count the unique number of Employee Names in cell H5 under the heading “Count of Unique Names”.

Sample Dataset of Counting Unique Names

Step 1:

The generic formula is,

=SUMPRODUCT(1/COUNTIF(range,criteria))
  • Insert the values into the function and the final form of the formula is,
=SUMPRODUCT(1/COUNTIF(B5:B15,B5:B15))

Where,

  • The range and Criteria are B5:B15.
  • The COUNTIF function looks into the data range and count the number of times each name appear in the data range {3;2;1;2;1;2;1;1;3;3;2}
  • After that, the result of the COUNTIF function is used as an advisor with 1 as the numerator. For this, numbers that have appeared only once in the array will become 1 and multiple appeared numbers will provide fractions as results.
  • Finally, the SUMPRODUCT function will count those 1 and will give the result.

Formula of SUMPRODUCT, and COUNTIF to Count Unique Names

  • Press ENTER to get the unique values.

Output with counting unique names

There is a flaw in this function that if there is a Blank Cell in the data set, then the formula will fail. Because the COUNTIF function generates “0” for each blank cell and 1 divided by 0 returns a divide by zero error (#DIV/0!).

DIV Error while counting unique names with blank cells

Step 2:

  • To overcome this situation let’s modify the formula a little bit. Now our new formula for this situation is,
=SUMPRODUCT(((B5:B15<>"")/COUNTIF(B5:B15,B5:B15&"")))

Formula of SUMPRODUCT and COUNTIF to count unique names with blank cells

Now if there is any blank cell in the dataset, the formula will ignore it.

  • Press ENTER to get the result.

Result with counting unique names


1.2. SUMPRODUCT with FREQUENCY

We will use the same data range that we used in the previous example.

Steps:

The generic formula is as follows,

=SUMPRODUCT(--(FREQUENCY(MATCH(Lookup_value,Lookup_array,[match_type])),ROW(reference)-ROW(reference.firstcell)+1),1))
  • Insert the values to get the final form-
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B15,B5:B15,0),ROW(B5:B15)-ROW(B5)+1)>0))

Where,

  • The MATCH function is used to get the position of each name that appears in the data. Here in the MATCH function the lookup_value, lookup_array and [match type] is B5:B15,B5:B15,0.
  • The bins_array argument is constructed from this part of the formula (ROW(B5:B15)-ROW(B5)+1)
  • The FREQUENCY function returns an array of numbers which indicates a count for each number in the array of data, organized by bin. A key feature in the operation of the FREQUENCY formula is that when a number has already been counted, FREQUENCY will return zero.
  • Now, we check for values that are greater than zero (>0), which converts the numbers to TRUE or FALSE, then we use a double-negative (– –) to convert the TRUE and FALSE values to 1s and 0s.
  • Finally, the SUMPRODUCT function simply adds the numbers up and returns the total.

Formula of SUMPRODUCT and FREQUENCY to count unique names

  • Since this is an Array Formula, press “CTRL+SHIFT+ENTER” to apply the formula and we have got our final count.

Output counting unique names from a range

Read more: Count Unique Values with Criteria by SUMPRODUCT in Excel


2. Utilizing SUMPRODUCT Under AND Criteria to Count Unique Names with Criteria

If you want you can also utilize the SUMPRODUCT function under AND criteria to count unique names with criteria.

Suppose we have criteria given in cell (H5) which is “M”. Now we will generate unique names regarding the criteria.

Sample dataset for counting unique names with criteria

Steps:

  • First, choose a cell (H8) and write the below formula down-
=SUMPRODUCT((C5:C15=H5)*(1/COUNTIF(B5:B15,B5:B15)))

Formula of SUMPRODUCT under AND Criteria to Count Unique Names with Criteria

  • Next, hit the ENTER key to get the count of unique names. Simple isn’t it?

Output with counted unique names with criteria

To gather more ideas about the applied formula check out the first method.


3. Using SUM with COUNTIF Formula to Count Unique Names

Here we have come out with another solution to count unique names. Follow the instructions below-

Steps:

  • Now we will use the SUM with COUNTIF formula to get the required count.

The generic formula for this formula is,

=SUM(IF(ISTEXT(Value),1/COUNTIF(range, criteria), ""))
  • Insert the values to get the final form of the formula-
=SUM(IF(ISTEXT(B5:B15),1/COUNTIF(B5:B15,B5:B15),""))

Where,

  • The ISTEXT function returns TRUE for all the values that are text and false for other values.
  • Range and Criteria are B5:B15
  • If the values is a text value, the COUNTIF function looks into the data range and count the number of times each names appear in data range {3;2;1;2;1;2;1;1;3;3;2}
  • The SUM function computes the sum of all the values and returns the result.

Formula of SUM, IF, ISTEXT, COUNTIF to count unique names

  • Since this is an Array Formula, press “CTRL+SHIFT+ENTER” to apply the formula and we have got our final count.

Final result of counting unique names

Read More: Excel Formula Count Unique Values (3 Easy Ways)


4. Using SUM with FREQUENCY and MATCH to Count Unique Names

In some situations, you can also use combine the SUM, FREQUENCY, and MATCH functions to count unique names.

The generic formula is,

=SUM(IF(FREQUENCY(IF(logical test<>"", MATCH(Lookup_value,Lookup_array,[match type])),ROW(reference)-ROW(reference.firstcell)+1),1))

Steps:

  • The final formula after the value insertion is,
=SUM(IF(FREQUENCY(IF(B5:B15<>"",MATCH(B5:B15,B5:B15,0)),ROW(B5:B15)-ROW(B5)+1),1))

Where,

  • Here in the MATCH function the lookup_value, lookup_array and [match type] is B5:B15,B5:B15,0
  • After the MATCH function, there is an IF The reason the IF function is needed is that MATCH will return a #N/A error for empty cells. So, we are excluding the empty cells with B5:B15<>””
  • The bins_array argument is constructed from this part of the formula (ROW(B5:B15)-ROW(B5)+1)
  • This resulting array is fed to the FREQUENCY function which returns an array of numbers that indicate a count for each number in the array of data
  • Finally the outer IF function indicates each unique value to 1 and duplicate value to 0.

SUM with FREQUENCY and MATCH Formula to Count Unique Names

  • Press “CTRL+SHIFT+ENTER” to apply the array formula.

Output without any duplicates

Read More: Count Unique Text Values with Criteria in Excel (5 Methods)


Similar Readings


5. Using UNIQUE Function to Count Unique Names

The UNIQUE function is available for only the Excel 365 version.

Step 1:

  • Now apply the UNIQUE function. The generic formula is,
=COUNTA(UNIQUE(range))
  • After inputting the values, the final form is,
=COUNTA(UNIQUE(B5:B15))

Combination of COUNTA and UNIQUE function to get data without duplicates

  • Press ENTER to get the result.

Output with counted unique names

Step 2:

  • You can also get the list of unique names by using this UNIQUE For this, the formula is,
=UNIQUE(B5:B15)

Formula of UNIQUE function to collect data without duplicates

  • Press ENTER to continue.

Output with names without duplicates

Read More: Excel VBA: Count Unique Values in a Column (3 Methods)


6. Using Advanced Filter to Count Unique Names in Excel

We can also use the Advanced Filter option to count unique names. To do that-

Steps:

  • Go to Data, in the Sort & Filter group, and click on Advanced.

Clicking Advanced Sort and Filter feature from Data tab

  • Advanced Filter window appears.
  • Here check on Copy to Another Location and Use Unique Records Only.
  • Now choose the data source for the List Range ($B$5:$B$15), Criteria Range ($B$5:$B$15), and Copy to $H$5. Click OK to continue.

Choosing List Range, Criteria Range and new location

  • And our list of unique names is made.

Output with unique names in a new column

  • To count the unique names, just use this formula and press ENTER key-
=ROWS(H5:H11)

Output applying ROWS function

Read More: Count Unique Values with Criteria by COUNTIFS in EXCEL (4 Examples)


Quick Notes

➤ If there is a blank cell in the dataset when you are using SUMPRODUCT with COUNTIF formula, the result will show divide by zero error (#DIV/0!)

➤ For the Array Formula, you have to Press “CTRL+SHIFT+ENTER” simultaneously to get the result.

➤ The UNIQUE function is only available for Excel 365. Users of older versions of Excel won’t be able to use the function.


Conclusion

Today we learned some procedures to count unique names from a dataset. If you have any confusion or suggestions, you are most welcome to share your thoughts in the comment section.


Related Articles

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo