How To Count Unique Values In Excel

This article will show you how to spot outliers with ease

Kevin Pocock

Last Updated on July 19, 2022

Any kind of list will contain both unique and distinct values. Unique values are those that only appear once in a list, while distinct values are all the values that appear within a list.

So, for example, if you had a list of numbers containing the values ‘1, 4, 7, 5, 7, 4’; then the unique values would be ‘1 and 5’ while the distinct values would be ‘1, 4, 7, and 5.’

When working in Excel, there may be occasions where you will need to identify how many unique values are contained within a list. This guide will teach you how to count unique values in Excel using a variety of different functions.

Counting Unique Values With COUNTIF

To count the number of unique values within a given range, you will need to use a combination of the ‘SUM’ and ‘COUNTIF’ functions.

The complete formula for this will look something like:

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

In the ‘data’ fields, you need to include the range of cells that contains your list.

Activate it by pressing ‘CTRL+SHIFT+ENTER’.

In order to understand how this formula works, we need to understand the various functions used within it.

The ‘Sum’ function adds data together and is controlled by the ‘IF’ function, which ensures that the only values that are added together are those that appear once.

There are other functions that you can use to search for a specific kind of unique value.

To search for only unique numerical values, then you should alter the formula to:

=SUM(IF(ISNUMBER(data)*COUNTIF(data, data)=1,1,0))

Alternatively, if you want to search for unique entries in a list made up of text, then you would use the formula:

=SUM(IF(ISTEXT(data)*COUNTIF(data, data)=1,1,0))

These formulas will count how many unique values are present in your list, but it won’t tell you what these unique values are.

Counting Unique Or Distinct Values With A Filter

You can extract all of the distinct values within a list by using the filters option. The advantage of this method is that it will create a list of all the distinct values within your set of data.

Using a separate function to determine how many of each value is present in the list will make this a useful method for determining what the unique values are within your data set.

First navigate to the ‘Data’ tab on the main ribbon and select the ‘Advanced’ option in the ‘Sort & Filter’ section.

This will open up a new window allowing you to decide how you would like to sort through your list.

To prevent your list of distinct values altering your original data, you will need to click on the box labeled ‘Copy to another location’. Next, select an empty cell for the start of your list.

You can do this by entering the cell’s name in the ‘Copy To’ box, or by minimizing the window and clicking on your chosen cell

Make sure the option labeled ‘Unique Records Only’ is ticked, and then press ‘Ok’ to start compiling your list.

A new list will be created containing only the distinct values within your original data set.

Conclusion

The above are just two ways to count the number of unique or distinct values within a given list.

Combining these tricks with other functions will allow you to see exactly what the unique values in your data set are, as well as how many of them are present.

This information will be useful for companies and individuals looking for anomalous data, or trying to understand recent trends across a data set.