How To Count Unique Values In Excel

by

In this tutorial, we will discuss how to count unique values in Excel. This can be useful in a variety of scenarios, such as when you want to analyze your data and extract insights.

Method 1: Using the COUNTIF function

The COUNTIF function in Excel is used for counting cells within a range that meet a certain condition. We can use this to count unique values.

Steps:

  1. Assume the range of cells from which you want to count unique values is A1:A10.
  2. In cell B1, enter the following formula: =1/COUNTIF($A$1:$A$10, A1)
  3. Copy this formula down to other cells in column B. This formula calculates the reciprocal of the count of each value in column A.
  4. In another cell, say B11, use the SUM function to add up all the values in column B: =SUM(B1:B10)
  5. The result in B11 is the count of unique values in the range A1:A10.

Method 2: Using the Advanced Filter

The Advanced Filter is a tool in Excel which can be used to extract unique values from a list.

Steps:

  1. Select your range of data (A1:A10).
  2. Go to Data > Sort & Filter > Advanced.
  3. In the Advanced Filter dialog box, select ‘Copy to another location’.
  4. In the ‘Copy to’ box, enter the address of the top cell where you want to paste the unique values. For example, B1.
  5. Check ‘Unique records only’ and click OK. Now, you have a list of unique values in column B.
  6. To get the count of unique values, you can use the COUNTA function: =COUNTA(B1:B10)

That’s it! Now you know how to count unique values in Excel using two different methods. Happy analyzing!