Table of Contents
To count duplicate values in Excel while avoiding multiple counting of the same value, you can use the COUNTIF function along with the UNIQUE function. The COUNTIF function allows you to count the occurrences of a specific value in a range, while the UNIQUE function creates a list of unique values from a range. By combining these two functions, you can accurately count the number of duplicate values in a range without counting the same value multiple times. This method is useful for organizing and analyzing data in Excel.
Excel: Count Duplicate Values Only Once
You can use the following formula in Excel to count the unique values in a particular range while only counting duplicate values once:
=SUMPRODUCT((A2:A12<>"")/COUNTIF(A2:A12,A2:A12&""))
This particular formula will count the number of unique values in the range A2:A12 while only counting duplicate values once.
The following example shows how to use this formula in practice.
Example: Only Count Duplicate Values Once in Excel
Suppose we have the following dataset that contains information about sales made by various employees at some company:
Suppose we would like to count the number of unique names in column A and only count duplicate values once.
We can type the following formula into cell E2 to do so:
=SUMPRODUCT((A2:A12<>"")/COUNTIF(A2:A12,A2:A12&""))
The following screenshot shows how to use this formula in practice:
The output tells us that there are 6 unique names in column A.
This formula was able to count the number of unique names while only counting duplicates once.
For example, there were four cells equal to “Frank” but only one of these “Frank” cells was actually counted.
To actually view a list of the six unique names, we can type the following formula into cell E2:
=UNIQUE(A2:A12)
The formula returns the six unique names in column A:
- Andy
- Bob
- Chad
- Derrick
- Eric
- Frank
Each of these names occurs at least once in column A and some of the names occur multiple times.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I count duplicate values in Excel, but only count each value once?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-count-duplicate-values-in-excel-but-only-count-each-value-once/
stats writer. "How can I count duplicate values in Excel, but only count each value once?." PSYCHOLOGICAL SCALES, 23 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-count-duplicate-values-in-excel-but-only-count-each-value-once/.
stats writer. "How can I count duplicate values in Excel, but only count each value once?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-count-duplicate-values-in-excel-but-only-count-each-value-once/.
stats writer (2024) 'How can I count duplicate values in Excel, but only count each value once?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-count-duplicate-values-in-excel-but-only-count-each-value-once/.
[1] stats writer, "How can I count duplicate values in Excel, but only count each value once?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I count duplicate values in Excel, but only count each value once?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.