How to Count Unique Values in Excel 2016?

It becomes messy to sort out the unique values in a large range of cells if it contains duplicate values. Let’s say if you want to identify or count unique values in excel, then you can build up your own formula for it.

There are various functions available in Excel using a combination of which you can implement the formula to count the unique values in Excel. In this section we will discuss some handy methods to count the unique values in excel:

1. Count Unique Values using Formula
2. Handle Blanks in the Selected Range
3. Count Unique Values that are Text
4. Count Unique Values that are Number
5. Using Advanced Filter

Steps to Count Unique Values in Excel 2016:

Count Unique Values Using Formula

As above we have discussed that you can build your own formula to count the unique values in a range of cells. Here, we will see the construction of two formulas to count unique values.

1^st formula

=SUM(IF(FREQUENCY(B2:B18,B2:B18)>0,1))

Now, let’s understand this formula

FREQUENCY: This function counts how many times the value has occurred in the selected range. It returns a vertical array which shows the frequency of occurrence of each number in the range.

Note: This function only considers non zero numeric value i.e. it avoids the text values and zeros.

Example: Let us check the frequency of each number in the range B2:B18. For this, first select an output-range (equal to the size of the reference range) and at the top cell of output-range type the formula:

=FREQUENCY(B2:B18, B2:B18)          (press Ctrl+Shift+Enter, as it an array formula)

See in the image below, at first occurrence of the value it shows, its total occurrence in the reference range, at rest occurrences, it shows 0.

Like at first occurrence of 100, it shows digit 6, it means 100 occurs 6 times in the reference range, at the rest occurrence of 100 it shows 0.

Now coming back to our main formula:

=SUM(IF(FREQUENCY(B2:B18,B2:B18)>0,1))

As we have seen in the figure above FREQUENCY function returns an array, calculating the occurrences of each value in the reference range. The array returned by FREQUENCY function is:

{6, 3, 0, 2, 0, 1, 1, 1, 0, 0, 0, 2, 0, 1, 0, 0, 0}

Now, IF function in the main formula checks each value of the array return by FREQUENCY function and for every non zero value it returns 1

IF({6, 3, 0, 2, 0, 1, 1, 1, 0, 0, 0, 2, 0, 1, 0, 0, 0}>0,1)

Result of IF: (1, 1, 1, 1, 1, 1, 1, 1)

The sum function adds all the 1s and generates the final result i.e. 8

=SUM(1, 1, 1, 1, 1, 1, 1, 1)

See in the image below the result of the formula:

=SUM(IF(FREQUENCY(B2:B18,B2:B18)>0,1))      (press Ctrl+Shift+Enter, as it an array formula)

2^st formula

=SUMPRODUCT(1/COUNTIF(B2:B18,B2:B18))

Let’s see how this formula is going to work for us. Initiating with COUNTIF, unlike FREQUENCY, COUNTIF works for both text and numeric data as well.

Note: The COUNTIF function shows the total number of occurrences for the value each time it appears in the reference range.

In the picture below you can see how COUNTIF function works on text data:

=COUNTIF(A2:A18,A2:A18)           (press Ctrl+Shift+Enter, as it a array formula)

Observe the figure above, Charles is repeated four times in the reference range and at each occurrence of Charles, COUNTIF displays digit 4.

The picture below shows how COUNTIF works for numeric data. 100 appears six times, so, for each occurrence of 100 it shows digit 6.

=COUNTIF(B2:B18,B2:B18)     (press Ctrl+Shift+Enter, as it a array formula)

Coming back to our main formula:

=SUM(1/COUNTIF(B2:B18,B2:B18))

The COUNTIF in above formula returns the array {6, 3, 6, 2, 3, 1, 1, 1, 6, 2, 6, 2, 6, 1, 6, 3, 2} and the entire array values are divided by 1 now, the SUM formula will have:

=SUM(0.1666, 0.3333, 0.1666, 0.5, 0.3333, 1, 1, 1, 0.1666, 0.5, 0.1666, 0.5, 0.1666, 1, 0.1666, 0.3333, 0.5)

When this SUM executes the result it generates is 8

The picture below shows the result for the formula:

=SUM(1/COUNTIF(B2:B18,B2:B18))        (press Ctrl+Shift+Enter, as it a array formula)

For text data the formula below shows the result:

=SUM(1/COUNTIF(A2:A18,A2:A18))      (press Ctrl+Shift+Enter, as it a array formula)

Handle Blanks in the Selected Range

Guess what will happen to the above formulas if the selected range has a blank cell/s. Then it would return the error #DIV/0!. You can observe it in the image below.

 

Well, a little change in the formula used above would help if we modify it in a way:

=SUM(1/COUNTIF(A2:A18,A2:A18&””))   (press Ctrl+Shift+Enter, as it a array formula)

You can observe the result in the picture above, it is counting blank cell as a unique entry and is counting 1 for it.

To avoid the counting of the blank cell we will modify the formula in a way:

=SUM(1/COUNTIF(A2:A18,A2:A18&””))-COUNTIF(A2:A18,”<>”&”*”)           (press Ctrl+Shift+Enter, as it a array formula)

The formula COUNTIF(A2:A18,”<>”&”*”) returns 1  as it checks for the cell that are

Now you can see it showing the correct result now.

Count Unique Values that are Text

In this section, we will see what we can do if have the reference range that has both numeric and text data and we want to count unique values only in the context of text.

The formula for this is:

=SUM(ISTEXT(A2:A10)/COUNTIF(A2:A10, A2:A10&””))           (press Ctrl+Shift+Enter, as it a array formula)

You can see in the picture above that it counting only the unique text value by avoiding the blank cells and cells having the numeric value.

Count Unique Values that are Number

For counting the unique value in the context of number in a range of cell that has text data, numeric data and blank cells we constructed the following formula:

=SUM(ISNUMBER(A2:A10)/COUNTIF(A2:A10, A2:A10&””))

In the image below you can see the result obtained by implementing the formula above:

Using Advanced Filter

Step 1: This is an inbuilt function in Excel, for this, you have to go to the Data tab and in Sort & Filter group click on Advanced.

Step 2: An Advanced Filter dialog box will pop out. Check the radio button “Copy to another location”.

In the list, range box selects the range from where you want to derive the unique record.

In Copy to box select the range where you want to copy the unique record.

Check Unique record only box.

Advanced Filter for Text data:

Advanced Filter for numeric data:

That was all about how you can count unique values in Excel. We have seen various method and formulas. It, not a limit to it you can explore it more ab constructs your own formula for it.