How to Count Duplicate Values Only Once in Excel (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, I will show you the procedures of counting duplicate values only once in Excel. While working 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. dataset of excel count duplicate values only once

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.


How to Count Duplicate Values Only Once in Excel: 4 Easy Ways

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.

dataset of excel count duplicate values only once

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


1. Counting Case Sensitive Duplicate Values Only Once in Excel

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.

Steps:

  • First, select the F5 cell.
  • Then, write down a formula in the selected cell. The formula will be the following one.
=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))
  • Then, press the Enter button.

excel count case sensitive duplicate values only once

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

excel count case sensitive duplicate values only once

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

excel count case sensitive duplicate values only once

In this way, you can count case-sensitive duplicate values only once in Excel.

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


2. Combining SUM, FREQUENCY, & MATCH Functions to Count Duplicates Only Once in Excel

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

Along with these functions, IF and the LEN function will be used.

Steps:

  • Select the F5 cell first.
  • Then, write the formula in the selected cell. Then, press the Enter button.
=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))

🔎 How Does the Formula Work?

  • 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. I have changed the first letter of the first name into lowercase, but the duplicate count remains the same.

This is the procedure for how you can count duplicate values only once in Excel using a combination of SUM, FREQUENCY, and MATCH functions.

Read More: How to Use COUNTIF Formula to Find Duplicates


3. Applying Combination of SUMPRODUCT & COUNTIF Functions

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

Steps:

  • After selecting the F5 cell, write down the following formula. Then, press the Enter button.
=SUMPRODUCT((B5:B20<>"")/COUNTIF(B5:B20,B5:B20&""))

Using sumproduct and countif functions to excel count duplicate values only once

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

Using sumproduct and countif functions to excel count 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.

Using sumproduct and countif functions to excel count duplicate values only once

This is the procedure for how you can count duplicate values only once in Excel using a combination of SUMPRODUCT and COUNTIF functions.

Read More: How to Count Repeated Words in Excel


4. Counting Duplicate Numeric Values Only Once in Excel

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.

Steps:

  • First select the F5 cell.
  • Then, copy the following formula. Then, press the Enter button.
=SUM(IF(FREQUENCY(D5:D20,D5:D20)>0,1))

Using excel count numeric duplicate values only once

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

In this way, you can count numeric duplicate values.

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


Practice Workbook

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


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.


Further Readings


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shakil Ahmed
Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo