How to Count Unique Values in Excel [Formulas + VBA]

 


For this, you need to use a method which will count value only one time and ignore it’s all the other occurrences in the list.

In Excel, you can use different methods to get a count of unique values.

It depends that which type of values you have so that you can use the best method for it.

 

6 Methods to Get a Count of Unique Values from a List of Values

With these methods, you can get a count of unique values in different situations and with different types of data.

1. Use Advanced Filter to Get a Count of Unique Values

Using advanced filter is one of the easiest ways to check the count of unique values and you don’t even need complex formulas.

Here we have a list of names and from this list, you need to count the number of unique names.

a list to count unique values

Following are the steps you need to follow to get the unique values:

  • First of all, select any of the cells from the list.
    select-a-cell-to-count-unique-values-min
  • After that, go to Data Tab ➜ Sort & Filter ➜ Click on Advanced.
    click-on-advance-to-count-unique-values
  • Once you click on it, you will get a pop-up window to apply advanced filters.
  • Now from this window, select “Copy to another location”.
  • In “Copy to”, select a blank cell where you want to paste unique values.
  • Now, tick mark “Unique Records Only” and click OK.
    select-range-with-advance-filter-to-count-unique-values
  • At this point, you have a list of unique values.
    you-will-get-list-of-unique-values-to-count-unique-values
  • Now, go to the cell below the last cell of the list and insert the following formula and hit enter.
=COUNTA(B2:B10)

It will return the count of unique value from that list of names.

count unique values using advanced filters and counta

Now you have a list of unique values and count as well.

This method is simple and easy to follow as you don’t need to write complex formulas for this.

2. Combination SUM and COUNTIF to Count Unique Values 

If you want to find the count of unique values in a single cell without extracting a separate list, then you can use a combination of SUM and COUNIF.

In this method, you just have to refer to the list of the values and the formula will return the number of unique values.

This is an array formula, so you need to enter it as an array, and while entering it use Ctrl + Shift + Enter.

And the formula is:

=SUM(1/COUNTIF(A2:A17,A2:A17))

When you enter this formula as an array it will look something like this.

{=SUM(1/COUNTIF(A2:A17,A2:A17))}
count unique values with sum countif