You are currently viewing How to count without duplicates in Excel

How to count without duplicates in Excel

Data analysis usually involves large data sets, and at some point, one may need to find out the number of values that appear only once in the dataset. In this case, you will be counting without duplicates. Unique values are values that appear only once in a dataset.

If you are faced with mountains of data, then counting without duplicates might be very arduous. More so, Excel does not have a special formula for counting without duplicates. However, there is always a way out! This tutorial will serve as a guide on how to count unique and distinct values without duplicate










Take a look at the numbers in the table above; the unique values are not duplicated. They don't appear more than once. Whereas, distinct values are the different numbers in the collection. In the table below, we have separated the unique values from a distinct values.










How do we count without duplicating?

1. You can do this efficiently by combining SUM and COUNTIF functions. A combo of two functions can count unique values without duplication. Below is the syntax:

=SUM(IF(1/COUNTIF(data, data)=1,1,0)).

2. With the COUNTIF formula l, you can count the frequency of occurrence of each value within the range.

What you will have as a result will look like this: {1;2;2;1;2;2;2;1;2}.

3. The formula contains three separate functions – SUM, IF, and COUNTIF.

COUNTIF function" counts how many times a particular number appears within the range. Please take a look at the answer and see it for yourself.

"IF function" analysis the results returned by the "COUNTIF" function. It maintains the 1's for unique values and replaces other values with "0."

4. Hence, the result, IF(1;2;2;1;2;2;2;1;2) = 1,1,0.

5. The array becomes {1;0;0;1;0;0;0;1;0}.

All the "1s" are unique values while the "0s" are duplicate values.

=SUM(IF(COUNTIF(A2:A10, A2:A10)=1,1,0))

Note: Always press Ctrl + Shift + Enter when entering your array formula.










How to count numeric values without duplicating

The function we used earlier counts, both texts and numbers, without duplicating. To count only numerals without duplicating, you have to include ISNUMBER function in the formula for finding unique values.


Note: Always press Ctrl + Shift + Enter when entering your array formula.

Meanwhile, the lowdown in this function is that it also counts dates and times.









How to count text values in Excel without duplicating

You can count the number of texts without duplicating by including the ISTEXT function in the array formula as stated below:


This formula will display the number of unique texts. It excludes errors, blank cells, logical numbers, numbers, etc.

Always press Ctrl + Shift + Enter when entering your array formula.










I hope this tutorial is comprehensive. Kindly share with friends & thanks for reading!