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.

**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”**.

**Steps:**

- In cell
**H5,**apply the**SUMPRODUCT**function with the**COUNTIF function**.

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.

- Press
**ENTER**to return the unique values.

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!).**

- To overcome this situation let’s modify the formula a bit:

`=SUMPRODUCT(((B5:B15<>"")/COUNTIF(B5:B15,B5:B15&"")))`

** **

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

- Press
**ENTER**to get the result.

**1.2 – Combining SUMPRODUCT with FREQUENCY**

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

**Steps:**

- Apply the
**SUMPRODUCT**function combined with the**FREQUENCY function**to get the unique names.

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**1**s and**0s**. - Finally, the
**SUMPRODUCT**function simply adds the numbers up and returns the total.

- Since this is an
**Array Formula**, press**“CTRL+SHIFT+ENTER”**to apply the formula.

We have our final count.

**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.

**Steps:**

- Choose a cell (
**H8**) and enter the formula below:

`=SUMPRODUCT((C5:C15=H5)*(1/COUNTIF(B5:B15,B5:B15)))`

- Press
**ENTER**to return the count of the unique names.

**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.

- Since this is an
**Array Formula**, press**“CTRL+SHIFT+ENTER”**to apply the formula.

We have our final count.

**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.

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

**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))`

- Press
**ENTER**to get the result.

**Step 2:**

- We can also get the list of unique names by using this
**UNIQUE**formula:

`=UNIQUE(B5:B15)`

- Press
**ENTER**to return the result.

**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**.

** **

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.

Our list of unique names is complete.

- To count the unique names, use this formula and press
**ENTER**:

`=ROWS(H5:H11)`

**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**