# 8 ways to count unique values ​​in Microsoft Excel (2023)

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

When analyzing data in Excel, knowing the number of unique items in a column can provide an invaluable overview of your data.

This statistic can help you quickly identify errors or inconsistencies, since values ​​that occur only once can sometimes be outliers.

This post will teach you how to get count of unique values ​​in Excel. Download a copy of the sample file used in this post and get involved!

## Unique vs Distinctive: What's the Difference?

The term "unique" is often confused with "unique". They have very different meanings and lead to different calculations.

UniqueThe values ​​are those that appear only once in the list. Unique values ​​from the list`{A, B, B, C, C, D}`If`{ADVERTISEMENT}`and the number will be 2.

SeparatedValues ​​are a complete set of distinct values. Distinguish values ​​from a list`{A, B, B, C, C, D}`If`{A B C D}`. In this case, the number is 4.

💡Top: Check out this post if you're really lookingNumber of different items.

## Count unique values ​​with the COUNTIFS function

TheCOUNTERThe function allows you to count the number of items in a range that meet a set of conditions.

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

``= SUM ( 1 * ( COUNTER ( B5:B14, B5:B14 ) = 1 ) )``

The formula above counts the number of individual items in the assortmentB5:B14.

``= COUNTER ( B5:B14, B5:B14 )``

TheCOUNTERThe function returns the number of occurrences of each element in the range. Since unique items only appear once, they are number 1.

Checking if it is equal to 1 returns an arrayTRUEILIEvalues ​​whereTRUEThe values ​​indicate that the item is unique.

(Video) How to Count Unique Text Values in a Range in Excel

Multiply this array by 1 and it will be convertedTRUEValues ​​up to 1 andLIEvalues ​​to 0.

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

📝note: You may need to enter this formula withcontrol button+Change+Inputfor older versions of Excel that don't have dynamic arrays.

## Count unique values ​​with the UNIQUE function

TheUNIQUEThe function is specifically designed to get a list of unique or unique values ​​from a list.

It can be combined withNUMBERa function that counts the number of unique values.

``= NUMBER ( UNIQUE ( B5:B14, FALSE, TRUE ) )``

The formula above counts the individual items in the assortmentB5:B14.

TheUNIQUEThe function returns a list of unique values. this versionUNIQUEThe function takes the second and third optional arguments it is set toLIEITRUEappropriate.

• The second argument is set toLIEand tellsUNIQUEa function that returns unique rows from array input.
• The third argument is set toTRUEand tellsUNIQUEFunction that returns elements from the input array that occur exactly once.

TheNUMBERThe function is then used to count the number of items returnedUNIQUEFunction.

## Count unique values ​​in conditional format

Conditional formatting is a great way to visually recognize data that matches a specific rule. These cells can be highlighted with a fill color.

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

Then you can filter the list by cell color to show only unique values ​​in the list.

Then counting becomes easier. You can use either ASUBTOTALFunction to count only visible cells or get a count from the status bar.

1. Select the range of values ​​in which you want to count unique values.
2. Go toDomBody.
1. click onConditional formattingcommand onStyletape section.
2. ChooseHighlight cell rulesPossibility.
3. Choosedouble valuesOption from the Options submenu.
2. Choose any color option. Just make sure you don't already use one of these colors in your data.
3. PressOKClick the button to apply the conditional formatting rule to the selected range.

You will now see that all values ​​that are unique are highlighted in your range.

``=SUBTOTAL(103,B5:B14)``
1. Add the aboveSUBTOTALEnter a formula anywhere on your worksheet to count the visible cells in a range

You can now filter based on these colored cells to only get unique itemsSUBTOTALto count.

1. Select an area.
2. Go toDaneBody.
3. click onFilterCommand to add filter switches to the pane.

💡Top: You can also usecontrol button+Change+LKeyboard shortcut to add filter toggles to the pane.

1. Click on the filter switch.
2. ChooseFilter by colorPossibility.
3. Select the color used to highlight the unique value.

You have now specified the number of unique values ​​in your dataSUBTOTALFunction.

## Count unique values ​​with 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.

(Video) How to Count Unique Values with Multiple Condition in Excel

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

Then you can use the value filter in the pivot table to filter the results for those where the number is 1.

This will only show unique items and calculate the grand total for the pivot table using unique items from your dataset.

First you mustCreate a pivot table based on your data set.

How to add a pivot table.

1. select data.
2. Go toSetBody.
3. ChooseturntablePossibility.
1. Choose one of the twoNew worksheetdie anAn existing worksheetfor the location of the new pivot table.
2. PressOKClick the button to create a pivot table.
1. Add a field for countingexcitementAreaPivotTable-FeldGlas.
2. Add a field for countingValuesAreaPivotTable-FeldGlas.

After adding a text box to the fileValuesarea, by default it is onTo counttype of aggregation.

You should now see a list of all the items in the rows along with the number of those items in values. Each element with a value of 1 is a unique element of your data.

1. Click the Sort and filter rows switch.
2. Choosevalue filterPossibility.
3. ChooseIs equal toPossibility.
1. Chooseto countbox on the left.
2. Enter a value1on the right side.
3. PressOKTaste.

This will filter out the pivot table only for the items whose count value is 1 and therefore only unique items will be displayed. Now the grand total shows the total number of unique items!

## Count unique values ​​with a DAX metric

For regular pivot tables, relatively limited aggregation types are available under Values. However, you can use the Data Model feature to create advanced calculations.

That way, you don't have to filter the pivot table to count the unique values, it does it solely based on the formula in Pivot Table Values.

When creating a PivotTable, make sure the data model is enabled.

1. Check overAdd this data to the data modelOption wPivot table from table or rangeMenu.

This allows you to create key figures using the DAX formula languageWertarea of ​​your pivot table.

1. Right-click on the tablePivotTable-FeldGlas.

This will open the Clock Editor.

1. Give your new measure a name likeUnique number.
``=VAR mySummary = SUMMARY (range, range[brand], „Unique“, IF (NUM(range[brand] ) = 1, 1, 0 ) )RETURN SUMX (mysummary, [unique] )``
2. PressOKClick the button to add a measure to the data model.
1. Click and drag your box thereexcitementArea.
2. Click and drag the new measureValuesArea.

This creates a Z variableSUMMARIZEFunction. This summarizes the dataMakeColumn and creates an aggregated column namedUniquewith calculation`IF ( COUN( Range[Marke] ) = 1, 1, 0 )`. This shows 1 for unique items and 0 otherwise.

TheADDITIVEThe function is then used to iterate through the virtual summarymy summaryTable variable and totalsUniqueSplit.

The pivot table will display 1 if the array element has a unique value, otherwise 0. The total also reflects the total number of unique items.

## Count unique values ​​with Power Query column distribution

Power Query previews your data statistics in the query editor.

(Video) How to Count the Occurrences of a Number or Text in a Range in Excel : Using Excel

This includes displaying the number of unique items for each column in the data viewer.

Here's how you can enter data into the Power Query Editor.

1. select data.
2. Go toDaneBody.
3. ChooseFrom table/rangeCommand.

This will open the Power Query Editor and you'll see a preview of your data. Also, a unique number should appear at the top of each column.

If you don't see these column distribution statistics, you can turn them onviewBody.

1. Go toviewim Power Query-Editor.
2. Check overcolumn distributionPossibility.

## Count unique values ​​with Power Query transform

You can also get a unique count of items in Power Query by performing some data transformation steps.

You can group your data by a total number to find all items that occur once.

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

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

Here are the steps to group your data.

1. Go totransformBody.
2. click onGroup bycommand onTischSection.
1. ChooseBasicPossibility.
2. Select the column you want to count unique values ​​from.
3. ChooseCount the rowsoptions forOperation.
4. PressOKTaste.

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

Now you can filter this data.

1. Click the filter switch in the number column.
2. Choosenumber filterPossibility.
3. ChooseIs equal toPossibility.
2. PressOKTaste.

Now you can count the results.

1. Go totransformBody.
2. click onCount the rowsCommand.

This will give you the number of unique items! Here you get a single value that you can load back into Excel or use in other queries.

## Count unique values ​​with VBA

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

You can create a custom function with VBA code. You can use it like any other function in the grid and simplify the structure of the formula compared to previous formula solutions.

Go todeveloperTab and clickVisual BasicCommand to open the Visual Basic Editor. You can also pressAlt+F11Keyboard shortcut to directly open the Visual Basic Editorif you don't see the Developer tab on the ribbon.

Now you need to create a new module to place your code. Go toSetClick the Visual Basic Editor tab and select the fileModuleoption from the menu.

(Video) Get A Distinct Count Report With Dynamic Array Formulas!

``Public function COUNTUNIQUEVALUES(rng As Range) As IntegeruniqueCount = 0For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count If Application.WorksheetFunction.CountIfs(rng, rng.Cells(i, j)) = 1 Then unique number = unique number + 1 end if next jNext iCOUNTUNIQUEVALUES = unique numberEnd function``

Paste the code above into the module.

It uses it`Application.Sheet-Funktion`access possibilityCOUNTERfrom the table and use it in your code to calculate the number of unique elements.

If the number of articles zCOUNTERThe function is 1 and the number of unique elements`unique number`increases by 1.

``= COUNT UNIQUE VALUES ( B5:B14 )``

Now you can use the above formula to count the unique values ​​in a rangeB5:B14.

## Count unique values ​​with Office Scripts

Another way to count unique values ​​with code is using Office Scripts.

You can create an Office Script that you can use to select a range in a worksheet and return the number of items that appear only once.

Go toautomateBookmark and selectNew scriptPossibility. This will open the Office ScriptCode-Editorwhere to write your code.

``function main(workbook: ExcelScript.Workbook) { let selectedRange = workbook.getSelectedRange(); Let selected values ​​= selected range. getValues(); let row height = selected area. getRowCount(); let colWidth = selectedRange.getColumnCount(); var allItems: (string | number | boolean) [] = []; var unique elements: (string | number | boolean)[] = []; //Step through the selected range and add values ​​//to a one-dimensional array for (let i = 0; i < rowHeight; i++) { for (let j = 0; j < colWidth; j++) { allItems. push(selectedValues[ and ][J]); }; }; for (let i = 0; i < allItems.length; i++) { if (allItems.indexOf(allItems[i], allItems.indexOf(allItems[i]) + 1) == -1) { uniqueItems.push(allItems [AND]); }; }; console.log(unique elements.length);}``

Paste the code aboveCode-Editorand pressSave the scriptTaste.

Now you can select an area in the sheet and pressStartw-TasteCode-Editor. The code runs and returns a number in the console log output.

This code iterates through all the cells in the selected range and adds them to a one-dimensional array called`all elements`.

Then all elements in the file are searched`all elements`Array and use the method to check if it is a duplicate element`Index`Method.

The`Index`The method gets the array index number of the first occurrence of the element. The trick is that you use this method to find the next index for the current element. If there is no next index, then`Index`returns -1 and the item is unique!

The unique elements are then added to another array named`unique items`.

Number of items in`unique items`The array is then returned with the extension in the console logsLongMethod.

## Conclusions

There is no specific function in Excel for counting the number of unique items in a list. However, there are many interesting ways to achieve this.

ACOUNTERLubUNIQUEFormulas will probably be the simplest and most dynamic option. Other features such as conditional formatting and filters may not be appropriate.

PivotTables or Power Query are good choices for situations that require repeated analysis.

(Video) How to Count Unique Values in Excel | How to Use the COUNTA and UNIQUE Functions

You can create a custom VBA function solution to easily implement the formula in the grid.

Office scripts can also be used, but must be sent to the console log or to a specific cell in the grid.

Have you used any of these methods to get a unique number? Let me know how it went in the comments!

## FAQs

### How do I count unique responses in Excel? ›

Count the number of unique values by using a filter
1. Select the range of cells, or make sure the active cell is in a table. ...
2. On the Data tab, in the Sort & Filter group, click Advanced. ...
3. Click Copy to another location.
4. In the Copy to box, enter a cell reference. ...
5. Select the Unique records only check box, and click OK.

How do you count how many unique values in a range in Excel? ›

In this case, you can use a combination of SUM, IF and COUNTIF functions to count unique values in Excel. To count unique values, enter the formula =SUM(IF(COUNTIF(range, range)=1,1,0)) in the desired cell. The range denotes the starting cell and the ending cell.

How do I count unique values in a spreadsheet? ›

How to use the COUNTUNIQUE formula in Google Sheets
• Type “=COUNTUNIQUE” or go to “Insert” → “Function” (or directly navigate to the “Functions” icon) → “Math” → “COUNTUNIQUE”.
• Select a range or an array in which you want to count the number of distinctive values.
• Press the “Enter” key.

How do I list unique values in Excel by criteria? ›

Get a list of unique values based on criteria. To extract unique values with condition, use the Excel UNIQUE and FILTER functions together: The FILTER function limits the data only to values that meet the condition. The UNIQUE function removes duplicates from the filtered list.

What is the unique formula in Excel? ›

The UNIQUE function will return an array, which will spill if it's the final result of a formula. This means that Excel will dynamically create the appropriate sized array range when you press ENTER.

How do I find unique values between two columns in Excel? ›

Navigate to the "Home" option and select duplicate values in the toolbar. Next, navigate to Conditional Formatting in Excel Option. A new window will appear on the screen with options to select "Duplicate" and "Unique" values. You can compare the two columns with matching values or unique values.

How do you count the number of unique values in an array? ›

You can use the following methods to count unique values in a NumPy array:
1. Method 1: Display Unique Values np. unique(my_array)
2. Method 2: Count Number of Unique Values len(np. unique(my_array))
3. Method 3: Count Occurrences of Each Unique Value np. unique(my_array, return_counts=True)
Jul 3, 2022

How do I count the number of unique values in a column in R? ›

How to Count Distinct Values in R
1. df <- data. frame(team=c('A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'),
2. points=c(106, 106, 108, 110, 209, 209, 122, 212),
3. assists=c(203, 206, 204, 202, 24, 25, 125, 119))
4. df.
5. team points assists.
6. 1 A 106 203.
7. 2 A 106 206.
8. 3 A 108 204.
Jun 7, 2022

What does distinct count do? ›

The COUNT DISTINCT function returns the number of unique values in the column or expression, as the following example shows. SELECT COUNT (DISTINCT item_num) FROM items; If the COUNT DISTINCT function encounters NULL values, it ignores them unless every value in the specified column is NULL.

How do you count values in Excel? ›

Use the COUNT function to get the number of entries in a number field that is in a range or array of numbers. For example, you can enter the following formula to count the numbers in the range A1:A20: =COUNT(A1:A20). In this example, if five of the cells in the range contain numbers, the result is 5.

### How do I count cells with specific text in Excel? ›

Count Cells with a Particular Text Value

Let me show you an example. Suppose you want to count the occurrences of the word “two” in a range of cells. You can just enter the formula in the =COUNTIF(A3:A12,”two”) to count the occurrences of the word “two” in the given range of cells A3 to A12.

How do I automatically create a list of unique values in Excel? ›

1. Highlight the cell range you want to analyze.
2. Navigate to the Conditional Formatting button on the Home tab.
3. Select Highlight Cells Rules.
4. Select Duplicate Values…
5. Click OK.
Aug 23, 2020

How do I count unique values in a pivot table? ›

To see the unique counts in a pivot table:
1. Create a Pivot Table from this data, with Region in the Rows area.
2. Add Unique, Units and Value in the Values area.
3. Change the "Unique" heading to "Count of Person" or "Person "
Nov 8, 2022

What is unique formula in sheets? ›

Returns unique rows in the provided source range, discarding duplicates. Rows are returned in the order in which they first appear in the source range.

How do you check if values in a column are unique? ›

select count(distinct column_name), count(column_name) from table_name; If the # of unique values is equal to the total # of values, then all values are unique.

How do I count unique combinations of two columns? ›

1. Select a blank cell besides the columns you will count unique rows, says Cell E2, type the formula =A2&B2&C2 into it, and then drag the cell's AutoFill Handle down to the range as you need.

What is the Excel shortcut to get the unique values in a column? ›

To quickly select the unique or distinct list including column headers, filter unique values, click on any cell in the unique list, and then press Ctrl + A.

How do I count occurrence of unique values inside a list? ›

Use Numpy to Count Unique Values in a Python List
1. We imported numpy as np and created an array using the array() function.
2. We used the unique() function from numpy to remove any duplicates.
3. Finally, we calculated the length of that array.
Sep 8, 2021

What is the number of unique values in a list? ›

The number of unique values in a list is the number of elements excluding duplicates. For example, the number of unique values in [1, 1, 2, 2, 3] is 3. Let us look at the below ways and introduce different methods to count unique values inside a list.

What is the count distinct problem? ›

In computer science, the count-distinct problem (also known in applied mathematics as the cardinality estimation problem) is the problem of finding the number of distinct elements in a data stream with repeated elements. This is a well-known problem with numerous applications.

### How do you count the number of occurrences for each unique value in a column of a Dataframe? ›

How do you Count the Number of Occurrences in a data frame? To count the number of occurrences in e.g. a column in a dataframe you can use Pandas value_counts() method. For example, if you type df['condition']. value_counts() you will get the frequency of each unique value in the column “condition”.

How to check unique values in multiple columns in R? ›

Filtering for Unique Values in R- Using the dplyr
1. Method 1: In one column, filter for unique values. df %>% distinct(var1) df %>% distinct(var1) ...
2. Method 2: Filtering for Unique Values in Multiple Columns. df %>% distinct(var1, var2) ...
3. Method 3: In all columns, filter for unique values. df %>% distinct()
Jun 12, 2022

How do I count the number of unique rows in a Dataframe in R? ›

For example, if we have a data frame called df that contains multiple columns then the number of unique values in each row of df can be found by using the command apply(df,1,function(x) length(unique(x))).

How do you assign a number to unique values in Excel? ›

Assign a Serial Number to Unique Values in Excel

To assign serial numbers to unique values, click on an empty cell, in our case cell B2, and enter the formula =IF(COUNTIF(A\$2:A2,A2)=1,MAX(B\$1:B1)+1,VLOOKUP(A2,A\$1:B1,2,0)) in the formula box. Click on Enter to get the first result as shown in the below image.

## Videos

1. 2 Quick Ways To Calculate Distinct Count With Pivot Tables
(Excel Campus - Jon)
2. Count Occurrences of Unique Values in a List in Excel
(TeachExcel)
3. How to Count Number of Unique Values with criteria in Excel | Microsoft Excel Tutorial
(MITutorials)
4. How to Count Unique Values in Microsoft Excel
(Xceleration - The Excel Learning Channel)
5. How To Find Unique Values Using Advanced Filter In Excel
(Excel Campus - Jon)
6. How to use COUNTIF and COUNTIFS in Microsoft Excel
(Kevin Stratvert)
Top Articles
Latest Posts
Article information

Author: Aracelis Kilback

Last Updated: 09/03/2023

Views: 6123

Rating: 4.3 / 5 (44 voted)

Author information

Name: Aracelis Kilback

Birthday: 1994-11-22

Address: Apt. 895 30151 Green Plain, Lake Mariela, RI 98141

Phone: +5992291857476

Job: Legal Officer

Hobby: LARPing, role-playing games, Slacklining, Reading, Inline skating, Brazilian jiu-jitsu, Dance

Introduction: My name is Aracelis Kilback, I am a nice, gentle, agreeable, joyous, attractive, combative, gifted person who loves writing and wants to share my knowledge and understanding with you.