Do you want to count the number of unique values in your Excel data?

Knowing the number of unique items in a column when performing data analysis in Excel can provide you with an invaluable overview of your data.

This statistic can help you quickly identify mistakes or inconsistencies since sometimes values that only appear once might be an outlier.

This post will show you how to get the count of unique values in Excel. Get your copy of the example file used in this post and follow along!

Unique vs Distinct: What’s the Difference?

The term unique is often mistaken for distinct. These have very different meanings and will result in different counts.

Unique values are those that only appear once in a list. The unique values from the list {A, B, B, C, C, D} are {A, D}, and the count will be 2.

Distinct values are the complete set of values that are different. The distinct values from the list {A, B, B, C, C, D} are {A, B, C, D}. The count in this case will be 4.

💡 Tip: Check out this post if you are really looking for a count of distinct items.

Count Unique Values with the COUNTIFS Function

The COUNTIFS function will allow you to count the number of items in a range that satisfy a set of conditions.

This can be used to count the unique items in a list.

= SUM ( 1 * ( COUNTIFS ( B5:B14, B5:B14 ) = 1 ) )

The above formula will count the number of unique items in the range B5:B14.

= COUNTIFS ( B5:B14, B5:B14 )

The COUNTIFS function will return the number of times each item appears in the range. Since the unique items only appear once, they will have a count of 1.

When you test if this is equal to 1, it returns an array of TRUE and FALSE values where the TRUE values will indicate the item is unique.

Multiply this array by 1 and it will convert the TRUE values to 1 and the FALSE values to 0.

This is summed up to get the count of unique items!

📝 Note: You might need to enter this formula using Ctrl + Shift + Enter for older versions of Excel that don’t have dynamic arrays.

Count Unique Values with the UNIQUE Function

The UNIQUE function is specifically designed to get the list of distinct or unique values from a list.

This can be combined with the COUNTA function to count the number of unique values.

= COUNTA ( UNIQUE ( B5:B14, FALSE, TRUE ) )

The above formula will count the unique items in the range B5:B14.

The UNIQUE function returns the list of unique values. This version of the UNIQUE function uses the second and third optional arguments which are set to FALSE and TRUE respectively.

• The second argument is set to FALSE and tells the UNIQUE function to return the unique rows from the array input.
• The third argument is set to TRUE and tells the UNIQUE function to return items from the input array that appear exactly once.

The COUNTA function is then used to count the number of items returned from the UNIQUE function.

Count Unique Values with the Conditional Format

Conditional formatting is a great way to visually spot data that satisfies a given rule. These cells can be highlighted with a fill color.

There is a conditional formatting rule that can be applied to highlight the unique values in a list.

You can then filter your list based on a cell color to show only the unique values in your list.

Then counting them becomes easy. You can either use a SUBTOTAL function to count only the visible cells or get the count from the status bar.

1. Select the range of values in which you want to count the unique values.
2. Go to the Home tab.

3. Click on the Conditional Formatting command in the Styles section of the ribbon.
4. Select the Highlight Cells Rules option.
5. Select the Duplicate Values option from the submenu options.

6. Select Unique from the Duplicate Values menu options.
7. Select any of the color options. Just make sure you are not already using any of these colors in your data.
8. Press the OK button to apply the conditional format rule to your selected range.

You will now see all the values that only appear once are highlighted in your range.

=SUBTOTAL(103,B5:B14)

9. Add the above SUBTOTAL formula somewhere in your sheet to count the visible cells in the range

You can now filter based on these colored cells to get only the unique items in the SUBTOTAL count.

10. Select the range.
11. Go to the Data tab.
12. Click on the Filter command to add filter toggles to the range.

💡 Tip: You can also use the Ctrl + Shift + L keyboard shortcut to add the filter toggles to the range.

13. Click on the filter toggle.
14. Choose the Filter by Color option.
15. Select the color used to highlight the unique value.

You now have a count of the unique values in your data given from the SUBTOTAL function.

Count Unique Values with a Pivot Table

Pivot Tables are a great tool for summarizing large amounts of data. They allow you to aggregate fields in many ways including counting results.

This means you can count the items in your list and display the count for each item.

You can then use a value filter on your pivot table to filter the results down to those where the count is 1.

This will show only the unique items and the grand total for the pivot table will be the found of unique items from your dataset.

First, you will need to create a pivot table based on your dataset.

Here’s how to add a pivot table.

1. Select the data.
2. Go to the Insert tab.
3. Select the Pivot Table option.

4. Select either a New Worksheet or an Existing Worksheet for the location of your new pivot table.
5. Press the OK button to create your pivot table.

6. Add the field to count in the Rows area of the PivotTable Field pane.
7. Add the field to count in the Values area of the PivotTable Field pane.

When you add a text field into the Values area, it will default to a Count type of aggregation.

You should now see a list of all the items in the rows along with a count of these items in the values. Any item that has a value of 1 is a unique item from your data.

8. Click on the sort and filter toggle of the rows.
9. Choose the Values Filters option.
10. Choose the Equals option.

11. Select the Count of field on the left.
12. Enter the value 1 on the right.
13. Press the OK button.

This will filter your pivot table on only those items where the count value is 1 and therefore only shows the unique items. Now the Grand Total will show the total count of unique items!

Count Unique Values with a DAX Measure

Regular pivot tables are fairly limited with the aggregation types available for the Values area, but you can use the Data Model feature to create more advanced calculations.

This way you won’t need to filter your pivot table to count the unique values, it will be done purely from a formula in the Values area of the pivot table.

When you create your pivot table make sure you enable the data model.

1. Check the Add this data to the Data Model option in the PivotTable from table or range menu.

This will allow you to create measures with the DAX formula language for the Value area of your pivot table.

2. Right-click on the table in the PivotTable Field pane.

This will open the measure editor.

3. Give your new measure a name such as Unique Count.

=VAR mySummary =
    SUMMARIZE (
        Range,
        Range[Make],
        "Unique", IF ( COUNTA ( Range[Make] ) = 1, 1, 0 )
    )
RETURN
    SUMX ( mySummary, [Unique] )

4. Add the above formula into the Formula area.
5. Press the OK button to add the measure to your data model.

6. Click and drag your field to the Rows area.
7. Click and drag the new measure to the Values area.

This creates a variable with the SUMMARIZE function. This summarizes the data by the Make column and creates an aggregated column named Unique with the calculation IF ( COUNTA ( Range[Make] ) = 1, 1, 0 ). This shows a 1 for unique items and 0 otherwise.

The SUMX function is then used to iterate over the summarized virtual mySummary table variable and sums the Unique column.

The pivot table will show a 1 when the field item has a unique value and 0 otherwise. The grand total will also reflect the total number of unique items.

Count Unique Values with Power Query Column Distribution

Power Query will show you preview statistics about your data in the query editor.

This includes showing a count of unique items for each column in the data preview.

Here’s how you can get the data into the power query editor.

1. Select the data.
2. Go to the Data tab.
3. Select the From Table/Range command.

This opens the power query editor and you will see a preview of your data. You should also see a unique count at the top of each column.

If you don’t see these column distribution statistics, then you can enable them from the View tab.

1. Go to the View tab in the power query editor.
2. Check the Column distribution option.

Count Unique Values with Power Query Transformation

You can also get the unique count of items in power query with a few data transformation steps.

You can group your data with a summarized count to find all the items that appear a single time.

You can filter the results based grouped items with a count of 1.

Then when you count this filtered results, you get the count of unique items from your data.

Here are the steps to group your data.

1. Go to the Transform tab.
2. Click on the Group By command in the Table section.

3. Select the Basic option.
4. Select the column from which you want to count unique values.
5. Select the Count Rows option for the Operation.
6. Press the OK button.

This creates a new data table with each item and the corresponding count of items.

Now you can filter this data.

7. Click on the filter toggle in the count column.
8. Select the Number Filters option.
9. Choose the Equals option.

10. Enter a 1 into the Filter Rows menu.
11. Press the OK button.

Now you can count the results.

12. Go to the Transform tab.
13. Click on the Count Rows command.

This gets you the count of unique items! You’ll get a single value here and you can load this back into Excel or use it in other queries.

Count Unique Values with VBA

Since there is now Excel function that counts unique values, one solution is to build your own.

You can create a user defined function with VBA code. You’ll be able to use this like any other function in the grid and it will simplify the formula structure as compared to previous formula solutions.

Go to the Developer tab and click on the Visual Basic command to open the visual basic editor. You can also press the Alt + F11 keyboard shortcut to directly open the visual basic editor if you don’t see the Developer tab in your ribbon.

Now you need to create a new module to place your code. Go to the Insert tab of the visual basic editor and select the Module option from the menu.

Public Function COUNTUNIQUEVALUES(rng As Range) As Integer

uniqueCount = 0

For i = 1 To rng.Rows.Count
    For j = 1 To rng.Columns.Count
        If Application.WorksheetFunction.CountIfs(rng, rng.Cells(i, j)) = 1 Then
            uniqueCount = uniqueCount + 1
        End If
    Next j
Next i

COUNTUNIQUEVALUES = uniqueCount

End Function

Paste the above code to the module.

This uses the Application.WorksheetFunction method to access the COUNTIFS function from the worksheet and use it in the code to calculate the number of unique items.

If the count of an item from the COUNTIFS function is 1, then the count of unique items uniqueCount is incremented by 1.

= COUNTUNIQUEVALUES ( B5:B14 )

Now you can use the above formula to count unique values in the range B5:B14.

Count Unique Values with Office Scripts

Another option to count the unique values with code is to use Office Scripts.

You can create an Office Script that will allow you to select a range in the sheet and return the count of items that only appear once.

Go to the Automate tab and select the New Script option. This opens the Office Script Code Editor where you can write your code.

function main(workbook: ExcelScript.Workbook) {

  let selectedRange = workbook.getSelectedRange();
  let selectedValues = selectedRange.getValues();
  let rowHeight = selectedRange.getRowCount();
  let colWidth = selectedRange.getColumnCount();

  var allItems: (string | number | boolean)[] = [];
  var uniqueItems: (string | number | boolean)[] = [];

  //Loop through selected range and add values
  //to 1 dimensional array
  for (let i = 0; i < rowHeight; i++) {
    for (let j = 0; j < colWidth; j++) {
      allItems.push(selectedValues[i][j]);
    };
  };

  for (let i = 0; i < allItems.length; i++) {
    if (allItems.indexOf(allItems[i], allItems.indexOf(allItems[i]) + 1) == -1) {
      uniqueItems.push(allItems[i]);
    };
  };
  console.log(uniqueItems.length);
}

Paste the above code to the Code Editor and press the Save script button.

Now you can select a range in the sheet and press the Run button in the Code Editor. The code will run and return the count in the console log output.

This code loops through all the cells in your selected range and adds them into a 1 dimensional array named allItems.

Then it will loop through all the items in the allItems array and check if it’s a duplicated item using the indexOf method.

The indexOf method finds the array index number of the first occurrence of an item. The trick is you use this method to find the next index for the current item. If there is no next index then the indexOf returns -1 and the item is unique!

Unique items are then added to another array named uniqueItems.

The count of the items in the uniqueItems array is then returned in the console logs with the length method.

Conclusions

There is no specific feature in Excel for finding the number of unique items in a list. But there are many interesting ways to get this.

A COUNTIFS or UNIQUE formulas solution will likely be the easiest and most dynamic option. Other features such as conditional format and filters might not be well suited.

For situations involving a repeated analysis, Pivot Tables or Power Query are good choices.

A custom VBA user-defined function solution can be built which makes the formula in the grid easier to implement.

Office Scripts can also be used, but will either need to output to the console log or a predetermined cell in the grid.

Have you implemented any of these methods for getting a unique count? Let me know how it went in the comments!