As Seen on TechCrunch: Finofo Raises $1.65M CAD!

Excel Guide

How to Count Unique Values in Excel: A Step-by-Step Guide

If you work with Microsoft Excel, chances are you've had to count unique values at some point. Whether you're trying to count the unique values in a single column or across multiple columns, the process is relatively straightforward. In this article, we'll walk you through the steps of counting unique values in Excel step by step, using a few different methods.

Method 1: Use the COUNTIF Function

The COUNTIF function is a built-in function in Excel that is used for counting cells that meet a certain criterion. In the context of counting unique values, we can use the COUNTIF function to count the number of cells that contain a certain value. For example, if we wanted to count the number of cells in a range that contain the value "1", we would use the following formula:

=COUNTIF(range,"1")

To count unique values, we can use the COUNTIF function in conjunction with the SUM function:

=SUM(COUNTIF(range,value1),COUNTIF(range,value2),...)

For a range with a limited number of values, you can use the following array formula:

=SUM(--(FREQUENCY(range,range)>0))

Remember to enter the formula as an array formula by pressing CTRL + SHIFT + ENTER.

Method 2: Use the UNIQUE Function

The UNIQUE function is a new function in Excel (Excel 365) designed to return a list of unique values from a range or array. To count unique values, you can use it in combination with the COUNT function:

=COUNT(UNIQUE(range))

This formula will work for both a range and an array. Again, remember to enter the formula as an array formula by pressing CTRL + SHIFT + ENTER.

Method 3: Use the FILTER Function

The FILTER function is another Excel 365 feature that filters a range or array based on specified criteria. To count unique values, you can use it along with the COUNT function:

=COUNT(FILTER(range,range=UNIQUE(range)))

Enter the formula as an array formula by pressing CTRL + SHIFT + ENTER.

Method 4: Use the SUMPRODUCT Function

The SUMPRODUCT function can be used for multiplying and summing ranges or arrays. To count unique values, you can use it along with the SUM function:

=SUM(SUMPRODUCT(--(range=value1)),SUMPRODUCT(--(range=value2)),...)

For a range with thousands of values, you can use the following array formula:

=SUM(SUMPRODUCT(--(FREQUENCY(IF(ISNUMBER(range),MATCH(range,range,0)),ROW(range))>0),ROW(range)))

Enter the formula as an array formula by pressing CTRL + SHIFT + ENTER.

Method 5: Use the COUNTIFS Function

The COUNTIFS function is used for counting cells that meet multiple criteria. To count unique values, you can use it in conjunction with the SUMPRODUCT function:

=SUMPRODUCT((FREQUENCY(IF(ISNUMBER(range),MATCH(range,range,0)),ROW(range))>0)*1)

Again, enter the formula as an array formula by pressing CTRL + SHIFT + ENTER.

Method 6: Use the VBA Script

For users familiar with Visual Basic for Applications (VBA), a script can be used to count unique values. Here's a basic example:

Sub CountUnique()
   Dim rng As Range
   Dim count As Double
   Set rng = Range("A1:A10")
   count = WorksheetFunction.CountIf(rng, "<>" & vbNullString)
   MsgBox count
End Sub

Method 7: Use the PivotTable

For large datasets, consider using a PivotTable:

  1. Open the workbook with the range you want to count unique values in.
  2. Select the range.
  3. Click Insert > PivotTable.
  4. Choose where to create the PivotTable.
  5. Click OK.

These methods provide various ways to count unique values in Excel based on your preferences and the version of Excel you are using. Choose the one that fits your needs and preferences.