Abrar
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:
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.
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:
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
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
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.
