## Dataset Overview

The dataset comprises several authors, some of their books, and the respective release years of the books. With this dataset, we’ll illustrate how to count duplicate values only once.

Note that we’re using a basic dataset to simplify things, in practical scenarios, you might encounter larger and more complex datasets.

## Method 1 – Counting Case Sensitive Duplicate Values Only Once in Excel

In this method, we’ll utilize a combination of functions including **SUM**, **IF**, **FREQUENCY**, **MATCH**, **TRANSPOSE**, **EXACT**, and **ROW **to count duplicate values only once.

**Steps:**

- Select cell
**F5**. - Enter the following formula into the selected cell:

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

- Press
**Enter**.

** How Does the Formula Work?**

- We have examined whether there is an empty cell or not within the range (
**B4:B9<>””**). If there is no empty cell, then we proceed to the**FREQUENCY**portion. - In the
**FREQUENCY**function, we have used the**TRANSPOSE**function to flip the columns and check whether an exact match is found or not. For**TRUE**value, we set the**MATCH**portion that returns the sequence number. - This value returned is then used as an argument to the
**FREQUENCY**function so that it can evaluate the corresponding values.**FREQUENCY**counts the number of unique values (typically it ignores text). - The value we get from this portion is going to divide the value 1. To eradicate the error, the
**IFERROR**function is used. - The
**SUM**function gets an array from the inside portion, and it does the additional task.

- We get the total authors.
- Remember, it is an array formula that’s why you need to press
**CTRL + SHIFT + ENTER**to execute the formula correctly. - This is a case-sensitive formula, if we change the case of the text, the result will change. We have written
in lowercase and the formula interpreted it as a different name.*james*

**Read More: **How to Ignore Blanks and Count Duplicates in Excel

## Method 2 – Combining SUM, FREQUENCY, & MATCH Functions to Count Duplicates Only Once in Excel

In this method, we’ll continue from the previous formula and use **SUM**, **FREQUENCY**, **MATCH**, **IF**, and **LEN** functions.

**Steps:**

- Select cell
**F5**. - Enter the following formula into the selected cell:

`=SUM(IF(FREQUENCY(IF(LEN(B5:B20)>0,MATCH(B5:B20,B5:B20,0),""), IF(LEN(B5:B20)>0,MATCH(B5:B20,B5:B20,0),""))>0,1))`

- Press
**Enter**.

** How Does the Formula Work?**

- The
**LEN**function finds the blank cells by checking if the length is greater than 0 (blank cells have a length of 0). You can see the**LEN**function is for the logical expression of**IF**. And for**TRUE**values, produced from**LEN**, we have a**MATCH**part that returns the position of a value in a range. - This value returned is then used as an argument to the
**FREQUENCY**function so that the corresponding text values can be evaluated.**FREQUENCY**counts the number of unique values (typically it ignores text). - For each occurrence of that same value after the first, this
**FREQUENCY**function returns a zero. For the time being, we didn’t set any value for 0 (**FALSE**) in**IF**. - We assign a value of 1 to each
**TRUE**condition within the**IF**function. Then we add the total by using**SUM**.

- As this is an array formula, press
**CTRL + SHIFT + ENTER**to execute the formula. This formula works in a case-insensitive manner. - Changing the case will not change the count result. We have changed the first letter of the first name into lowercase, but the duplicate count remains the same.

**Read More:** How to Use COUNTIF Formula to Find Duplicates

## Method 3 – Applying Combination of SUMPRODUCT & COUNTIF Functions

In this method, we’ll use **SUMPRODUCT **and **COUNTIF **functions to count duplicates in a case-insensitive manner.

**Steps:**

- Select cell
**F5**. - Enter the following
**formula**into the selected cell:

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

- Press
**Enter**.

** How Does the Formula Work?**

- The
**COUNTIF**portion of this formula returns an array of the count of each element in the cell. Then we divided 1 by each element of the array returned by**COUNTIF**. It will again return an array of fractions. - These array elements are the arguments of
**SUMPRODUCT**and the function sums the elements. Thus, we will find the count of duplicate values only once.

- This is a case-insensitive formula. I have changed the case of the first name. As you can see, the duplicate count is not changed.

**Read More: **How to Count Repeated Words in Excel

## Method 4 – Counting Duplicate Numeric Values Only Once in Excel

In this method, we’ll focus on counting duplicate numeric values only once using **SUM**, **IF**, and **FREQUENCY** functions.

**Steps:**

- Select cell
**F5**. - Enter the following
**formula**into the selected cell:

`=SUM(IF(FREQUENCY(D5:D20,D5:D20)>0,1))`

- Press
**Enter**.

** How Does the Formula Work?**

**FREQUENCY**counts the number of unique values within the range. For the first occurrence of a value, this function returns a number equal to the**number of occurrences of that value**.- For each occurrence of that same value after the first, this
**FREQUENCY**function returns a zero. We didn’t set any value for 0 (**FALSE**) in**IF**for the time being. - We assign a value of 1 to each
**TRUE**condition within the**IF**function. Then we add the total by using**SUM**.

- This formula only works for the numeric values, if we write the formula for text strings, we will get 0 in the result.

**Read More:** How to Count Duplicates with Pivot Table in Excel

**Practice Workbook**

You can download the practice workbook from here:

## Further Readings

**<< Go Back to Count Duplicates in Excel | Learn Excel**