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

In this article, we will demonstrate some methods to count unique names in Excel. Excel doesn’t have a built-in function to count unique values or text, but there are a variety of techniques and approaches by which we can count these distinct values.

In the following image is an overview of the methods we’ll use.

Overview Image of Counting Unique Names


Method 1 – Using the SUMPRODUCT Function to Count Unique Names in Excel

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.


1.1 – Combining SUMPRODUCT with COUNTIF

In the dataset below we have some Employee Names and their Gross Salary. Some Employee Names appear more than once. We’ll count the number of unique Employee Names in cell H5 under the heading “Count of Unique Names”.

Sample Dataset of Counting Unique Names

Steps:

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 counts the number of times each name appears.
    • Output: {3;2;1;2;1;2;1;1;3;3;2}
  • The result of the COUNTIF function is used as the divisor, with 1 as the numerator. As a result, numbers that appeared only once in the array become 1 and numbers with multiple instances provide fractions as results.
  • Finally, the SUMPRODUCT function will count the 1‘s and return the result.

Formula of SUMPRODUCT, and COUNTIF to Count Unique Names

  • Press ENTER to return the unique values.

Output with counting unique names

There is a flaw in this function. 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

  • To overcome this situation let’s modify the formula a bit:
=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 – Combining SUMPRODUCT with FREQUENCY

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

Steps:

The generic formula is:

=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] are 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 represent a count of 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.

We have our final count.

Output counting unique names from a range


Method 2 – Using SUMPRODUCT Under AND Logic to Count Unique Names with Criteria

We can also utilize the SUMPRODUCT function under AND logic to count unique names with criteria.

Suppose we have a criterion given in cell H5 which is “M”. Let’s count the unique names that match the criteria.

Sample dataset for counting unique names with criteria

Steps:

  • Choose a cell (H8) and enter the formula below:
=SUMPRODUCT((C5:C15=H5)*(1/COUNTIF(B5:B15,B5:B15)))

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

  • Press ENTER to return the count of the unique names.

Output with counted unique names with criteria


Method 3 – Using the SUM Formula with the COUNTIF Formula to Count Unique Names in Excel

Here’s a different approach.

Steps:

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

The generic 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.
  • The Range and Criteria are B5:B15
  • If the value is a text value, the COUNTIF function looks into the data range and counts the number of times each name appears in the data range.
    • Output: {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.

We have our final count.

Final result of counting unique names


Method 4 – Combining the SUM, FREQUENCY and MATCH Functions to Count Unique Names

In some situations, we can also 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 value insertion is:
=SUM(IF(FREQUENCY(IF(B5:B15<>"",MATCH(B5:B15,B5:B15,0)),ROW(B5:B15)-ROW(B5)+1),1))

Where,

  • In the MATCH function the lookup_value, lookup_array and [match type] are B5:B15,B5:B15,0.
  • After the MATCH function there is an IF, which is needed because MATCH will return an #N/A error for empty cells. We exclude the empty cells in the IF function 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 represent a count for each number in the array of data.
  • Finally, the outer IF function indicates each unique value as 1 and each duplicate value as 0.

SUM with FREQUENCY and MATCH Formula to Count Unique Names

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

Output without any duplicates


Method 5 – Using the UNIQUE Function to Count Unique Names in Excel

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

Step 1:

  • 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:

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

Formula of UNIQUE function to collect data without duplicates

  • Press ENTER to return the result.

Output with names without duplicates


Method 6 – Applying the Advanced Filter to Count Unique Names in Excel

We can also use the Advanced Filter option to count unique names.

Steps:

  • Go to the Data tab.
  • In the Sort & Filter group, click on Advanced.

Clicking Advanced Sort and Filter feature from Data tab

The Advanced Filter window appears.

  • Choose Copy to Another Location and check Use Unique Records Only.
  • Select 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

Our list of unique names is complete.

Output with unique names in a new column

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

Output applying ROWS function


Quick Notes

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

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

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


Download Practice Workbook


<< Go Back to Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Asikul Himel
Asikul Himel

Asikul Islam Himel, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology, has contributed over two years to the ExcelDemy project. Starting as an Excel & VBA Content Developer, now he manages projects at You Have Got This Math Project. He wrote 60+ articles for ExcelDemy, reviewed 500+, and focused on quality maintenance. Currently, his responsibilities include project management and team leadership. Himel's interests encompass data analysis, leadership, WordPress applications, and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo