Excel Productivity Guide

Get instant access to this free e-book fully loaded with Useful Excel Tips, 80+ Keyboard Shortcuts, and VBA Codes.

Count Unique Values in Excel

    how to count unique values in excel

    Let’s say you have a list of values where each value is entered more than once. And now, you want to count unique values from that list so that you can get the actual numbers of values which are there in the list.

    For this, you need to use a method which will count a 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.

    Here in this post, I will show you 5+1 different methods to count unique values. You can use these methods according to the type of the values you have.

    Download this sample data file from here to follow along.

    Table of Content

    1. Advanced Filters
    2. Using Sum and COUNT
    3. With SUMPRODUCT and COUNTIF
    4. Count Unique Text Values
    5. Count Unique Numbers
    6. VBA Code To Count Unique Values

    1. Advanced Filters

    Using advanced filter is one of the easiest ways to check the count of unique values. And in this method, 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.

    Please follow these simple step.

    • First of all, select any of the cells from the list.

    • After that, go to Data Tab -> Sort & Filter -> Click on Advanced.

    • 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.

    • At this point, you have a list of unique values.

    • 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.

    Now, you have a list of unique values and count as well. This method is simple and easy to follow. Ans I say you don’t have to write complex formulas for this.

    2. Use 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. One thing which you have to do along with this formula is you need to enter it as an array. While entering the formula you need to 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))}

    How Does It Work?

    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.

    First of all, you have used COUNIF function to count the number of each value from 16. Here, COUNTIF returns values like below.

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

    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.33 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. With SUMPRODUCT and 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 use enter a formula as an array.

    The formula you need to enter:

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

    And, as an array function.

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

    How Does It Work?

    This formula exactly works in the same way as you have learned in above method, the difference just you have used SUMPRODUCT instead of SUM. SUMPRODUCT function can take an array without using Ctrl + Shift + Enter.

    4. Count Unique Text Values

    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),””))

    Enter this formula as an array.

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

    How Does It Work?

    In this method, you have used IF function and ISTEXT function. ISTEXT function will first verify that all the values are text or not. And returns, TRUE or FALSE.

    After that, IF function will apply COUNTIF Function on all the text values and other values will remain blank.

    And, when you use SUM function it will return the unique count of text values.

    5. Count Unique Numbers

    And, if you just want to count unique numbers from a list of values, 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),””))}

    How Does It Work?

    In this method, you have used IF function and ISNUMBER function. ISNUMBER function will first verify that all the values are number or not. And returns, TRUE or FALSE.

    After that, IF function will apply COUNTIF Function on all the numeric values and other values will remain blank.

    And, when you use SUM function it will return the unique count of numeric values.

    6. VBA Code (UDF) To Count Unique Values

    Here I have VBA (UDF) code which can help you to count unique values without using any kind of formula. And for this code credit goes to ExcelTip.

    Here is the 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 fo to your worksheet and insert following formula.

    =CountUnique(range)

    Sample File

    download sample excel file

    Conclusion

    Counting unique values can be useful for you while working with large data sets. Like the name list which you have used here had duplicate names. And after calculating unique numbers you get that you have 10 unique names in the list.

    All the methods which you have learned here are super useful different situations. 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? Or, you have a different method. Please share with me in the comment box.

    I would love to hear from you.


    • Ogundepo Ezekiel Adebayo

      Advanced filter is the best.