Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Count Unique Values Using Excel Formula (4 Easy Ways)

Working with a large dataset in Excel, you may often need to know how many duplicate and unique values are there. You are definitely not going to want to count this manually and moreover, it can be time-consuming in most cases. This tutorial will show you how you can count unique values using Excel formula.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Functions You May Need to Know

1. COUNTIF

The COUNTIF function counts cells in a range that meets a particular condition.

Syntax of the COUNTIF function is as follows

COUNTIF (range, criteria)

Arguments:

Argument Required/Optional Explanation
range Required Range of cells where the criteria will be assigned for counting.
criteria Required Condition or criterion for the selected range of cells.

To know more about the COUNTIF function you can explore Microsoft Support site.

2. FREQUENCY

The FREQUENCY function calculates how often values occur within a range of values. It returns a vertical array of numbers.

Syntax of the FREQUENCY function is

FREQUENCY (data_array, bins_array)

Arguments:

Arguments Required/Optional Explanation
data_array Required It is an array of values for which you want to get frequencies.
bins_array Required It is an array of intervals for grouping values. The interval is also known as “bins”.

You can know more about the FREQUENCY function from Microsoft Support site.

3. SUMPRODUCT

The SUMPRODUCT function returns the sum of the products of corresponding ranges or arrays.

SUMPRODUCT(N number of array)

N can be any number equal or more than 1.

Arguments:

Argument Required or Optional Value
array 1 Required  The first array of numbers. 
[array2] Optional  The second array of numbers. 
[array3]  Optional The third array of numbers. 

To know more about the SUMPRODUCT function you can explore Microsoft Support site.

4. MATCH

The MATCH function is used to locate the position of a specified item in a range of cells. It returns the relative position of a particular item in the range.

Syntax of the MATCH function is

MATCH(lookup_value, lookup_array, match_type)

Arguments:

Argument Required/Optional Explanation
lookup_value Required The value to match in the array
lookup_array Required A range of cells or an array reference where to find value
match_type Optional Specifies how Excel matches lookup_value with values in lookup_array. Here, 1 = exact or next smallest, 0 = exact match and -1 = exact or next largest

For a deeper understanding, you can check out the Microsoft support site.


4 Simple Methods to Count Unique Values with Formula in Excel

In the following, I have described 4 simple and quick methods to count unique values with formula in Excel.

Here we have a concise data set of a few Employees with their Addresses and Salaries. For keeping things simpler we are using this basic set of data.

In the practical world, you may encounter many complex data sets and relationships.

Using the Employee Name we will show how to count unique text values and using the Salary column we will show how to do it for numbers.


1. Using Excel Advanced Sort & Filter Option

You can use the Sort & Filter option for counting unique values using formula in Excel.

Steps:

  • First, select the cells or column you want to extract unique values.
  • Then on the Data tab you will find the Sort & Filter Inside there, you will notice the Advanced option.
  • Click Advanced. A new dialog box will pop up in front of you.

Using Excel Advanced Sort & Filter Option to Count Unique Values in Excel

  • Now select Copy to another location and select range from the table.
  • Then, clicking the “Copy to” section choose a cell (F5) to copy unique values.
  • Do not forget to check Unique records only.
  • Finish the procedure clicking OK.

Using Excel Advanced Sort & Filter Option to Count Unique Values in Excel

  • You will get the unique values from your selected cells.

  • Now you can further count them using the ROWS function to count unique values. Simply, choose a cell (F14) and apply the below formula down-
=ROWS(F4:F10)

Using Excel Advanced Sort & Filter Option to Count Unique Values in Excel

  • Gently, hit ENTER and you will get the unique values counted in the selected cell.

Using Excel Advanced Sort & Filter Option to Count Unique Values in Excel

You can also use the COUNTA function as well, since it will count irrespective of the type of your values. Similarly, select a cell (F16 ) and write the below formula-

=COUNTA(F4:F10)

Using Excel Advanced Sort & Filter Option to Count Unique Values in Excel

  • Similarly, click ENTER and you will get the output in your hands.


2. Counting Unique Text Values Using Multiple Formula

2.1. Using Basic SUMPRODUCT and COUNTIF Functions

We will use SUMPRODUCT and COUNTIF functions to extract the number of unique presents within a range.

Steps:

  • Starting with, choose a cell (F5) and apply the below formula-
=SUMPRODUCT(1/COUNTIF(B5:B16,B5:B16))

Using Basic SUMPRODUCT and COUNTIF Functions to count unique values in Excel

Formula Breakdown:
  • Here our data_range and criteria_range will be the same. Because for each value in the data range, COUNTIF will check how many times that occurs in the criteria_range, so our data_range and criteria_range should be the same.
  • Inside mechanism of the COUNTIF function will result in an array that contains the occurrence number for each value. Then SUMPRODUCT will sum all the values inside the array and return the result.
  • It gave the total. There are a total of 7 unique names here.

  • 2.2. Combine IF, COUNTIF, and SUMPRODUCT Functions

    Sometimes you might find blank cells inside a range. In that case, after applying formula, you will get to see “#Div/0!” error. Well, with the combination of IF, COUNTIF, and SUMPRODUCT functions you can count unique text values whether there is a blank cell inside the range. Follow the instructions below-

    Steps:

    • Simply, choose a cell (F5) and write the formula down-
    =SUMPRODUCT(IF(B5:B16<>"",1/COUNTIF(B5:B16, B5:B16), 0))

    Combine IF, COUNTIF, and SUMPRODUCT Functions

    • Hence, press ENTER to get the output.

    ➥ Related: Count Unique Values with Criteria by SUMPRODUCT in Excel


    2.3. Using Array to Count

    So far we have discussed the formula which is useful for small ranges. The SUMPRODUCT-COUNTIF formula will get slower with the increase of range. To resolve this issue, we can use the MATCH and FREQUENCY functions.

    Step 1:

    • First, choose a cell (F5) and put the formula down-
    =SUMPRODUCT(IF(FREQUENCY(MATCH(B5:B16,B5:B16,0),MATCH(B5:B16,B5:B16,0))>0,1))

    Combine IF, COUNTIF, and SUMPRODUCT Functions to count unique values

    Formula Breakdown:
    • Here FREQUENCY function gives an array containing the number of occurrences for a value for the first occurrence and for the next occurrence of that value, it returns 0.
    • The MATCH function is used for returning the position in the range. These two MATCH functions returned the position and then used as an argument to the FREQUENCY function which gives us a count of the total number of distinct values.
  • Next, click the ENTER key from the keyboard to get the final output.
  • For keeping things simple and similar we are using the same set of examples, you will find this formula faster compared to the earlier one while dealing with a large data set. You can also try the combination of the ROW, MATCH, FREQUENCY, IF, SUMPRODUCT functions.

    Step 2:

    • Just like the previous methods, choose a cell (F5) and write the below formula inside the cell-
    =SUMPRODUCT(IF(FREQUENCY(IF(B5:B16<>"",MATCH(B5:B16,B5:B16,0)),ROW(B5:B16)-ROW(B5)+1),1))

    Using Array to Count unique values in Excel

    Formula Breakdown:
    • Again this formula uses the FREQUENCY function to count the unique values. And we will use the MATCH and ROW functions within the FREQUENCY function to provide its two placeholders.
    • The ROW function returns the row number of each cell in range. Since we subtracted the first – row number from each row, it returns an array of serial from the value 1.
    • The MATCH function in this formula will give us the position number of each item that appears in the data range. It will return values as an array.
    • Inside the IF statement to check for any empty cell. These arrays are fed to the FREQUENCY function which returns how often values occur within the set of data.
    • The outer IF function sets each unique value to 1 and duplicate value to FALSE. And the SUMPRODUCT counts the non-zero elements within the array and gives the total.
  • Similarly, hit ENTER to get the final output.
  • Using Array to Count unique values in Excel

    Read more: How to Use COUNTIF for Unique Text


    3. Count Unique Numbers

    3.1. Using FREQUENCY Function

    Counting unique numbers will be much easier using the FREQUENCY function. This function is typically for the numbers that’s why we couldn’t use it directly for the text values.

    Step 1:

    • Write the below formula down in the selected cell (F5)-
    =SUMPRODUCT(IF(FREQUENCY(D5:D16,D5:D16),1))

    Using FREQUENCY Function to count unique values in Excel

    Formula Breakdown:
    • Similarly, to the COUNTIF function, FREQUENCY function returns an array of each value occurrence. But only for numbers.
    • Here, the FREQUENCY function returns the occurrence number for the first one and 0 for the rest of the same value.
    • IF function will interpret the non-zero elements as TRUE and 0 as FALSE. For each non-zero value it will set 1 in the array. Then with the SUMPRODUCT function it will provide the total amount.
  • Thereafter, press ENTER and you will get the unique values counted.
  • Using FREQUENCY Function to count unique values in Excel

    Instead of the IF function we can write the formula using the SIGN function. This is the simplest Excel formula to count unique values.

    Step 2:

    • Presently, choose a cell (F5) and apply the below formula-
    =SUMPRODUCT(SIGN(FREQUENCY(D5:D16,D5:D16)))

    Using FREQUENCY Function to count unique values in Excel

    Formula Breakdown:
    • Earlier, we have set the IF function in such a way that it will replace non-zero as 1. But the SIGN function will do it automatically. SIGN interprets all the non-zero (positive) numbers as 1 and zero as 0.
    • If you can remember the array returned by the FREQUENCY function (can scroll up and see) then you will understand that for non-zero elements SIGN provided 1. Then use SUMPRODUCT to get the total output.
  • Thus, click ENTER to get the counting completed for unique numbers.

  • 3.2. Using ISNUMBER Function

    For counting unique numbers you can use the ISNUMBER function too. Follow the steps below-

    Steps:

    • Simply, choose a cell (F5) and apply the formula below-
    =SUMPRODUCT(1/COUNTIF(D5:D16,D5:D16),--ISNUMBER(D5:D16))

    Using ISNUMBER Function to count unique values in Excel

    Formula Breakdown:
    • The COUNTIF function here, will result in an array that contains the instance number for each value.
    • The ISNUMBER function here will result in an array of values TRUE for numbers and False for others. Adding two hyphens () before ISNUMBER will convert TRUE into 1.
  • Finish with, hitting the ENTER button to get the final output.
  • Using ISNUMBER Function to count unique values in Excel


    4. UNIQUE Function to Count Values

    If you are using EXCEL 365, then in order to count unique values, you can use the UNIQUE function as well.

    Steps:

    • First, select a cell (F5) and write the below formula inside the cell-
    =COUNTA(UNIQUE(B5:B16))

    Formula Breakdown:
    • The UNIQUE function will fetch the non-duplicate values and the COUNTA function will count the non empty cells within range.
    • So, using the UNIQUE function inside a COUNTA function leads us to the result we are looking for.
  • Simply, click ENTER and you will get your desired result. This same formula will work for numbers as well.
  • excel formula count unique values


    Conclusion

    That’s all for today. We have tried listing out several formulas to count unique values in Excel. Hope you will find this helpful. Feel free to comment if anything seems difficult to understand. You are welcome to notify us of any other methods to the task.


    Further Readings

    Shakil Ahmed

    Shakil Ahmed

    My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

    We will be happy to hear your thoughts

    Leave a reply

    ExcelDemy
    Logo