In Excel, we need to perform various count operations, i.g. **Count date occurrences, count text cells, count unique, counting duplicates**, and many more. Today we are going to show you ways to Excel count duplicate values** only once**. For this session, we are using Excel 2019, feel free to use yours.

Before diving into the session, let’s get to know about the dataset that is the base of our examples.

Here we have a table that contains several authors with a few of their books and the respective release years of the books. Using this dataset, we will count duplicate values only once.

Note that this is a basic dataset to keep things simple. In a practical scenario, you may encounter a much larger and complex dataset.

**Table of Contents**hide

## Practice Workbook

You are welcome to download the practice workbook from the link below.

## Count Duplicate Values Only Once

You must have noticed there are duplicate values in the dataset and have obviously understood that these are purposefully done. Now, let’s count the values.

We will store the count result in the *Count Duplicate* *Once* field, distinct to the table.

### 1. Count Duplicate Values that are Case Sensitive

Though it’s a counting operation, we will hardly use the **COUNT** function. Rather we need to use several other functions and form a formula combining the functions.

We will use the **SUM**, **IF**, **FREQUENCY**, **MATCH** functions to count duplicate values only once. Along with these functions, the **TRANSPOSE**, **EXACT**, **ROW** functions will also play a useful part in the formula.

The formula will be the following one

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

Here we have examined whether there is an empty cell or not within the range (** B4:B9<>””**). If there is no empty cell, then we get into 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 from here, 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 texts).

The value we get from this portion is going to divide the value 1. To eradicate error, the **IFERROR** function is used.

The **SUM** function gets an array from the inside portion and it does the additional task.

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

There is a problem that this is a case-sensitive formula, if we change the case of the text, the result will be changed.

Here we have written *james* in lowercase and the formula directed it as a different name.

### 2. Count Duplicates that are Case Insensitive

There are situations when you explicitly need case sensitivity to take place, apart from that it’s quite usual that you need to count duplicates regarding the cases.

In this section, we will see how to count duplicates only once with case insensitive.

#### I. Combination of SUM-FREQUENCY-MATCH

To continue the legacy(I) from the previous formula, we will use the core functions, **SUM**, **FREQUENCY**, **MATCH**, of that formula.

Along with these functions, **IF** and the **LEN** function will be used. Let’s write the formula first

`=SUM(IF(FREQUENCY(IF(LEN(B4:B19)>0,MATCH(B4:B19,B4:B19,0),""), IF(LEN(B4:B19)>0,MATCH(B4:B19,B4:B19,0),""))>0,1))`

The **LEN **function finds the blank cells checking 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 texts).

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

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.

#### II. Combination of SUMPRODUCT – COUNTIF

We can count the duplicates with case insensitive using **SUMPRODUCT** and **COUNTIF** functions.

The formula will be the following one

`=SUMPRODUCT((B4:B19<>"")/COUNTIF(B4:B19,B4:B19&"")) `

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. Changing the cases will not change the results.

### 3. Count Duplicate Numeric Values Only Once

The earlier formulas that we have used can be workable for both text and numeric values. If you want formulas only for numeric values then this section will be a helpful one. In this section, we will see how in Excel you can count duplicate numeric values only once. Let’s explore with us.

The formula will be formed using **SUM**, **IF**, and **FREQUENCY**. The formula will be the following one.

`=SUM(IF(FREQUENCY(D4:D19,D4:D19)>0,1)) `

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

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

## Conclusion

That’s all for the session. We have listed several approaches to Excel count duplicate values only once. Hope you will find this helpful. Feel free to comment if anything seems difficult to comprehend. Let us know any other methods that we might have missed here.