Often in a dataset, we want to estimate duplicate values once to actually determine the different entries and their number. In a large dataset, counting separate entities individually can be a tedious task. Excel has various functions which we can combine together to count duplicate values in Excel only once. In this article, we will be discussing that topic elaborately.

## 📁 Download Excel File

Download the Excel file below.

## Learn to Count Duplicate Values in Excel Only Once with These 3 Approaches

To demonstrate things easily, we will be using a Dataset to guide you through the steps to duplicate values only once in Excel. Suppose we have a list of famous directors and the movies they created along with the year of release. The sample datasheet is as follows:

Now we will be using this dataset to talk about the different methods of counting duplicate values. These are described as follows:

**Approach**

### 1. Counting Duplicate Values Only Once That Are Case Insensitive

In this approach, we will look into the way by which we can count duplicate values only once where the function we would like to use will be case insensitive. That is even if our dataset has two similar names with uppercase or lowercase or mixed case, the formula will treat all of them as a single value and won’t count the duplicate values. This can be done using a combination of functions in two ways. The two ways are discussed below.

**Approach 1.1**

#### 1.1 Integrating SUM, FREQUENCY, and MATCH Functions

The combination of the functions **SUM**, **FREQUENCY**, **MATCH**, **IF**, and **LEN** altogether will provide the formula for counting duplicate values only once in Excel. The syntax of these functions are briefly stated below:

**SUM** Function is given by

`=SUM(number1, [number2], [number3], ...)`

Where, **number1** = First value to sum

**number 2**= second value to add

**number 3** = Third value to sum.

**FREQUENCY** Function syntax is as follows:

`=FREQUENCY(data_array, bins_array)`

Where, **data_array** = The array of values for which we want to find out the frequencies.

**Bins_array** = An array of intervals for the values which are grouped.

The structure of the **MATCH** Function is as follows:

`=MATCH(lookup_value, lookup_array, [match_type])`

Where, **lookup_array**= The range of cells where we want to look for a value to match.

**lookup_value**= The value that we are looking for to match in the lookup array

**match_type**= 1, 0 or -1. 1= Exact value or next smallest value; 0= Exact match; -1= Exact value or the next largest value.

The syntax of IF Function is:

`=IF(logical_test, [value_if_true], [value_if_false])`

Where, **logical_test**= A logical expression which can be **TRUE** or **FALSE**.

**value_if_true**= Return value if the logic is **TRUE**.

**value_if_false**= Return value if the logic is **FALSE**.

**LEN** Function has the following syntax

`=LEN(text)`

Where, **text**= The text for which we need to calculate the length.

Using these functions together, we can count duplicate values following these steps:

⬇️⬇️ **STEPS **⬇️⬇️

- First, we create a cell with the heading Counting Duplicate Values Once.
- Next, we select the cell
**F6**and write the following formula in the formula bar.

`=SUM(IF(FREQUENCY(IF(LEN(B6:B17)>0,MATCH(B6:B17,B6:B17,0),""), IF(LEN(B6:B17)>0,MATCH(B6:B17,B6:B17,0),""))>0,1))`

**🔨 Formula Breakdown**

**SUM(IF(FREQUENCY(IF(LEN(B6:B17)>0,MATCH(B6:B17,B6:B17,0),””), IF(LEN(B6:B17)>0,MATCH(B6:B17,B6:B17,0),””))>0,1))**

👉 **LEN** function **LEN(B6:B17)** looks for blank cells that have a length higher than 0. Blank cells hold a length of zero.

👉 **LEN **function has embedded inside the** IF **function.

👉 For** TRUE** output from this** IF **function and** LEN **function, we match the output using the **MATCH **function that provides the place of a value in a range.

👉 The value we obtain from these functions is now passed as an argument to the **FREQUENCY** function. This function estimates the number of distinct or unique values. The function provides a 0 for the same value occurrence.

👉 We put a value of one for any **TRUE** condition inside the** IF** function.

👉 The **SUM** function sums up the returned values.

- After that, we press the keyboard button
**Ctrl+Shift+Enter**as it is an array formula. - The results will look like the following table:

**Approach 1.2**

#### 1.2 Implementing SUMPRODUCT and COUNTIF Functions

The two functions **SUMPRODUCT** and **COUNTIF** can be combined and used to count duplicate values only once in Excel. The syntax of these functions are as follows:

**SUMPRODUCT** Function:

`=SUMPRODUCT(array1, [array2], ...)`

where, **array1** (Required)= The first array to multiply and then add.

**array2** (Optional)=The second array to multiply and then add.

The steps for this process are as follows:

⬇️⬇️ **STEPS **⬇️⬇️

- First, we select cell
**F6**under Counting Duplicate Values Once. - Then we move over to the formula bar and write down the following formula:

`=SUMPRODUCT((B6:B17<>"")/COUNTIF(B6:B17,B6:B17&""))`

**🔨 Formula Breakdown**

**SUMPRODUCT((B6:B17<>””)/COUNTIF(B6:B17,B6:B17&””))**

👉 The **COUNTIF** function provides an array of the total count of any cell element. It is then divided by 1 to get the argument for **SUMPRODUCT** Function.

👉** The SUMPRODUCT **function then multiplies the arguments so that we get the count of duplicate values once.

- After that, we press
**Enter**. - The results will be similar to the following table. Since this is a case-insensitive case, altering the case won’t change the output result.

** Read More: ****8 Ways to Count Duplicate Values in Multiple Columns in Excel**

**Approach**

### 2. Counting Duplicate Values Only Once That Are Case Sensitive

For case-sensitive formulas, changing the case of the dataset will change the result. In this method, we will be using **SUM**,** IF**,** FREQUENCY**, **MATCH**, **TRANSPOSE**,** EXACT**, and **ROW** Functions. We are discussing the syntax of the functions now which we did not discuss in the earlier methods.

**TRANSPOSE** Function has the following syntax:

`=TRANSPOSE(array)`

Where **array** = The array or range of cells to transpose.

The **EXACT** Function has the following structure:

`=EXACT(text1, text2)`

Where **text1**= The first text string that we need to compare.

**text2**= The second text string that we need to compare.

The syntax of **ROW** Function is as follows:

`=ROW([reference])`

Where **reference**= reference to one cell or a range of cells.

To count duplicate values that is case sensitive, we need to follow these steps:

⬇️⬇️ **STEPS **⬇️⬇️

- First, we select cell
**F6**. - After that, we write down the following formula.

`=SUM(IFERROR(1/IF(B6:B17<>"", FREQUENCY(IF(EXACT(B6:B17, TRANSPOSE(B6:B17)), MATCH(ROW(B6:B17), ROW(B6:B17)), ""), MATCH(ROW(B6:B17), ROW(B6:B17))), 0), 0))`

**🔨 Formula Breakdown**

**SUM(IFERROR(1/IF(B6:B17<>””, FREQUENCY(IF(EXACT(B6:B17, TRANSPOSE(B6:B17)), MATCH(ROW(B6:B17), ROW(B6:B17)), “”), MATCH(ROW(B6:B17), ROW(B6:B17))), 0), 0))**

👉 Here we have checked if **B6:B17** cell range has any empty cell by** B6:B17<>””**.

👉 If there is no empty cell, the formula jumps to the **FREQUENCY** segment.

👉 The** TRANSPOSE** function flips the columns and checks if there is an exact match. If **TRUE** then we set the **MATCH** segment that returns the **sequence number**.

👉 The formula then uses the returned value as an argument for the **FREQUENCY **function. This function counts the number of distinct values.

👉 The formula then divides the value by 1. To avoid errors, we use **the IFERROR function**.

👉 The **SUM** function adds the return values obtained from the other functions.

- After writing down the formula, we press
**Ctrl+Shift+Enter**as it is an array formula. - The results will look like the following image. Here, as the formula is case-sensitive, so any change in the cases of two entries of the same name will be counted as a separate entry.

** Read More: ****7 Ways to Count Duplicates in Two Columns in Excel**

**Approach**

### 3. Estimating Duplicate Numeric Values Only Once

We can use the formulas mentioned so far to count duplicate values only once for texts and numbers. In this approach, we will be demonstrating how we could count duplicate numerical values once. The steps for this approach are:

⬇️⬇️ **STEPS **⬇️⬇️

- We select cell
**F6**. - After that, we write down the following formula in the formula bar and press
**Enter**.

`=SUM(IF(FREQUENCY(D6:D17,D6:D17)>0,1))`

**🔨 Formula Breakdown**

**SUM(IF(FREQUENCY(D6:D17,D6:D17)>0,1))**

👉 **FREQUENCY** function estimates the number of distinct or identical values within the specified range.

👉 This function returns a number for the first occurrence that is equal to how many times that value has occurred, and it returns zero for subsequent occurrences.

👉 We put a value of one to any **TRUE **case and the **SUM** function adds up those values.

- The results will be like the following image:

** Read More: ****4 Easy Methods to Count Occurrences per Day in Excel**

## 📄 Important Notes

🖊️ For array formulas, we must be careful to press **Ctrl+Shift+Enter** upon entering a formula.

🖊️ The third Formula for duplicating numerical values once **SUM(IF(FREQUENCY(D6:D17,D6:D17)>0,1)) **cannot be used for text values.

## 📝 Takeaways from This Article

You have taken the following summed-up inputs from the article:

📌 ** **We can use a combination of functions to count duplicate values only once in an Excel database.

📌 We can also use certain functions to count duplicate numerical values only once in Excel. Such formulas don’t work for text values.

## Conclusion

To sum up, these are the ways in which we can count duplicate values only once in Excel. I hope we all have got some insights about counting duplicate values. If you have any further questions, you can leave a comment below. Follow our page **ExcelDen** to know more about such informative articles.