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.

## Download Practice Workbook

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

## Count Unique Names in Excel (5 Methods)

### 1. Using SUMPRODUCT Function to Count Unique Names

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

#### i. SUMPRODUCT with COUNTIF

**Step-1:**

In the following situation, we are given a dataset where the names of some sales reps and their salary are given in the **â€śSales Repâ€ť** and **â€śSalaryâ€ť **columns. Now we have sales reps whose names appeared more than once. So we have to count the unique number of the sales repâ€™s name in cell** E4** under the heading **â€śCount Unique Namesâ€ť.**

**Step-2:**

Now in cell **E4, **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(B4:B17,B4:B17))**

Where,

**Range**and**Criteria**are**B4:B17**- The
**COUNTIF**function looks into the data range and count the number of times each names appear in data range**{3,2,1,2,3,3,4,3,3,1,1,1,1,1}** - 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.

Press Enter to get the unique values.

**Step-3:**

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

**Step-4:**

To overcome this situation letâ€™s modify the formula a little bit. Now our new formula for this situation is,

** **

**
=SUMPRODUCT(((B4:B17<>â€ťâ€ť)/COUNTIF(B4:B17,B4:B17&â€ťâ€ť)))**

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

Press** Enter** to get the result.

#### ii. SUMPRODUCT with FREQUENCY

**Step-1:**

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

Now apply the **SUMPRODUCT** with** FREQUENCY **function to get the unique names.

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(B4:B17,B4:B17,0),ROW(B4:B17)-ROW(B4)+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**B4:B17,B4:B17,0.** - The
**bins_array**argument is constructed from this part of the formula**(ROW(B4:B17)-ROW(B4)+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.

Since this is an **Array Formula**, press** â€śCTRL+SHIFT+ENTERâ€ť** to apply the formula. And we have got our final count.

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

### 2. Using SUM with COUNTIF Formula to Count Unique Names

**Step-1:**

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(B4:B17),1/COUNTIF(B4:B17,B4:B17),â€ťâ€ť))**

Where,

- The
**ISTEXT**function returns**TRUE**for all the values that are text and false for other values. - Range and Criteria are
**B4:B17** - 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,3,3,4,3,3,1,1,1,1,1}** - The
**SUM**function computes the sum of all the values and returns the result.

**Step-2: **

Since this is an **Array Formula**, press** â€śCTRL+SHIFT+ENTERâ€ť** to apply the formula. And we have got our final count.

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

### 3. Using SUM with FREQUENCY and MATCH Formula to Count Unique Names

**Step-1:**

Now we will use the **SUM **with **FREQUENCY **and **MATCH **formula 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))**

The final formula after the value insertion is,

**=SUM(IF(FREQUENCY(IF(B4:B17<>â€ťâ€ť,MATCH(B4:B17,B4:B17,0)),ROW(B4:B17)-ROW(B4)+1),1))**

Where,

- Here in the
**MATCH**function the**lookup_value**,**lookup_array**and**[match type]**is**B4:B17,B4:B17,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**B4:B17<>â€ťâ€ť** - The bins_array argument is constructed from this part of the formula
**(ROW(B4:B17)-ROW(B4)+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

Press **â€śCTRL+SHIFT+ENTERâ€ť **to apply the array formula.

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

**Similar Readings**

### 4. Using the UNIQUE Function to Count Unique Names

**Step-1:**

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

Now apply the **UNIQUE **function. The generic formula is,

**=COUNTA(UNIQUE(range))**

After inputting the values, the final form is,

**=COUNTA(UNIQUE(B4:B17))**

Press **Enter **to get the result.

**Step-2:**

You can also get the list of unique names by using this **UNIQUE **function. For this, the formula is,

Press Enter to continue.

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

### 5. Using Advanced Filter to Count Unique Names in Excel

**Step-1:**

We can also use the **Advanced Filter** option to count unique names. To do that, go to Data, in the **Sort & Filter** group, click on **Advanced.**

**Step-2:**

**Advanced Filter** window appears. Here check on** Copy to Another Location** and Use **Unique Records Only.**

**Step-3:**

Now choose the data source for the** List Range ($B$3:$B$17), Criteria Range ($B$3:$B$17), **and **Copy to $E$3**. Click **Ok** to continue.

And our list of the unique names is made.

**Step-4:**

To count the unique names, just use this formula,

**=ROWS(E4:E9)**

And press **Enter**.

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