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 duplicating them.

 

 

 

 

 

 

 

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 distinct values.

 

 

 

 

 

 

 

 

1. Using the array COUNTIF formula

The COUNTIF function counts the frequency of occurrence of each value within the range. To get the number of the unique values, you have to sum them up. 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(COUNTIF(data, data)=1,1,0)).

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

COUNTIF function" counts how many times a particular number appears within the range.

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

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

CTRL+SHIFT+ENTER allow excel to recognize the formula as an array function.  unique value= =SUM(IF(COUNTIF(A2:A10, A2:A10)=1,1,0))

Alternatively, you can use the SUMPRODUCT formula to avoid the use of CTRL+SHIFT+ENTER.

Below is the syntax:

=SUMPRODUCT(1/COUNTIF(range, criteria)).

In the example below, we have a list of items with duplicates.

If we use the SUM formula to count the total number of items, there will be duplicates. To exclude the duplicates, you have to follow these steps.

Step 1: Go to cell D1 and enter this formula “=SUMPRODUCT(1/COUNTIF( B1:B11,B1:B11)). B1:B11 is the array range you want to count the total number of unique values in the list.

Step 2: Press enter and the results will be displayed in cell D1. From the displayed results (6) we can see there are no duplicates.

The above formula counts the values of the six items A/B/C/D/E/F.  

2. Using a combination of SUM, IF, FREQUENCY, MATCH, and ROW Function

If we want to count unique values that exclude all duplicates (that appear in more than one product), use the combination of these functions.

  • The sum function allows you to add the values.
  • For each true condition of IF function, assign value 1.
  • The FREQUENCY function allows you to count the number of values ignoring texts and zeros. In the first occurrence of the distinct value, it returns an equal number to the number of occurrences of that value. It returns zero for the occurrences that have the same value after the first occurrence.
  • The MATCH function returns the position of the text value in an array range. The return value acts as the function argument for FREQUENCY.
  • The ROW function returns the row reference number. 

Example: Enter the following formula to count unique values by excluding all duplicates

=SUM(IF(FREQUENCY(MATCH(B1:B11,B1:B11,0),ROW(B1:B11)-ROW(B1)+1)=1,1))

Press enter. The unique value is 3 (Item D/E/F).

3. Using ISNUMBER Function to count numeric numbers

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.

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

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.

4. Using ISTEXT Function to count text values

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

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

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.

5. Using Pivot Table to count text values

Easies way to count the value is by creating the pivot table and using the Count of items

1. Create a pivot table

2. Use Count of Items

3. Output Table

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

%d bloggers like this: