How to Count Unique Values in Excel

1. Advanced Filters

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

Please follow these simple steps.

  • First of all, select any of the cells from the list.
select a cell to count unique values
  • 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. SUM and COUNTIF

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

How it works

To understand this formula you need to break it down into three parts and just remember that we have entered this formula as an array and there are total 16 values in this list, not unique but total.

Ok, so look.

In the first part, you have used COUNIF to count the number of each value from 16 and here COUNTIF returns values like below.

countif will count unique values

In the second part, you have divided all the values with 1 which returns value like this.

sum function will sum unique values

Important Point: Let’s say if a value is there in the list twice, then it will return 0.5 for both of the values so that in the end when you sum it, it becomes 1 and if a value is there for three times it will return 0.333 for each.

And, in the third part, you have simply used SUM function to sum all those values and you have a count of unique values.

This formula is quite powerful and it can help you to get the count in a single cell.

3. SUMPRODUCT + COUNTIF

In the last method, you have used SUM and COUNTIF method. But, you can also use SUMPRODUCT instead of SUM.

And, when you use SUMPRODUCT, you don’t need to enter a formula as an array.

The formula you need to enter is,

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

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

{=SUMPRODUCT(1/COUNTIF(A2:A17,A2:A17))}

sumproduct to count unique values

How it works

This formula exactly works in the same way as you have learned in above method, the difference is just that you have used SUMPRODUCT instead of SUM.

And, SUMPRODUCT can take an array without using Ctrl + Shift + Enter.

4. Unique Text

Now, let’s say you have a list of names in which you also have mobile numbers and you want to count unique values just from text values.

So, in this case, you can use below formula:

=SUM(IF(ISTEXT(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))

And when you enter this formula as an array.

{=SUM(IF(ISTEXT(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))}

count unique values only text

How it works

In this method, you have used IF function and ISTEXT.

ISTEXT first verifies that all the values are text or not and return TRUE if a value is a text.

istext to count unique values only text

After that, IF applies COUNTIF on all the text values where you have TRUE and other values remain blank.

if function count unique values only text

And in the end, SUM returns the sum all the unique values which are text and you get the count of unique text values this way.

5. Unique Numbers

And if you just want to count unique numbers from a list of values then you can use below formula.

=SUM(IF(ISNUMBER(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))

Enter this formula as an array.

{=SUM(IF(ISNUMBER(A2:A17),1/COUNTIF(A2:A17, A2:A17),””))}

count unique values only number

How it works

In this method, you have used IF function and ISNUMBER.

ISNUMBER first verifies that all the values are numeric or not and return TRUE if a value is a number.

After that, IF applies COUNTIF on all the numeric values where you have TRUE and other values remain blank.

And in the end, SUM returns the sum all the unique values which are numbers and you get the count of unique numbers this way.

6. VBA Code

Function CountUnique(ListRange As Range) As Integer
Dim CellValue As Variant
Dim UniqueValues As New Collection
Application.Volatile
On Error Resume Next
For Each CellValue In ListRange
UniqueValues.Add CellValue, CStr(CellValue) ' add the unique item
Next
CountUnique = UniqueValues.Count
End Function

Enter this function in your VBE by inserting a new module and after that go to your worksheet and insert following formula.

=CountUnique(range)

count unique values with vba

Sample File

Conclusion

Counting unique values can be useful for you while working with large datasets.

Like the name list which you have used here had duplicate names and after calculating unique numbers, we get that there are 10 unique names in the list.

Well, all the methods which you have learned here are useful in different situations and you can use anyone from these which you think is perfect for you.

If you ask me advanced filter and SUMPRODUCT is my favorites.

Now tell me one thing.

Which one is your favorite?

Please share your views with me in the comment section, I’d love to hear from you. And, don’t forget to share this tip with your friends.


  • Ogundepo Ezekiel Adebayo

    Advanced filter is the best.

  • Carlos Mario Castaño

    And there is another way to obtain a list of unique values: design a Pivot Table with names in the rows and Count Of Names in Values area.
    Sincerely yours,
    Carlos

    • Puneet Gogia

      Thanks Carlos, I just forgot to share this one.

  • naveen sharma

    I use another way, copy paste list to other sheet and remove duplicates.

    • Puneet Gogia

      Yes, that’s simple. Only thing, this is not dynamic but fine if you need to do this once.