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.

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.

3. click onConditional formattingcommand onStyletape section.
4. ChooseHighlight cell rulesPossibility.
5. Choosedouble valuesOption from the Options submenu.

6. ChooseUniqueout ofdouble valuesmenu items.
7. Choose any color option. Just make sure you don't already use one of these colors in your data.
8. 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)

9. 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.

10. Select an area.
11. Go toDaneBody.
12. 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.

13. Click on the filter switch.
14. ChooseFilter by colorPossibility.
15. 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.

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.

4. Choose one of the twoNew worksheetdie anAn existing worksheetfor the location of the new pivot table.
5. PressOKClick the button to create a pivot table.

6. Add a field for countingexcitementAreaPivotTable-FeldGlas.
7. 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.

8. Click the Sort and filter rows switch.
9. Choosevalue filterPossibility.
10. ChooseIs equal toPossibility.

11. Chooseto countbox on the left.
12. Enter a value1on the right side.
13. 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.

2. Right-click on the tablePivotTable-FeldGlas.

This will open the Clock Editor.

3. 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] )

4. Add the formula aboveFormulaArea.
5. PressOKClick the button to add a measure to the data model.

6. Click and drag your box thereexcitementArea.
7. Click and drag the new measureValuesArea.

This creates a Z variableSUMMARIZEFunction. This summarizes the dataMakeColumn and creates an aggregated column namedUniquewith calculationIF ( 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.

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.

3. ChooseBasicPossibility.
4. Select the column you want to count unique values ​​from.
5. ChooseCount the rowsoptions forOperation.
6. PressOKTaste.

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

Now you can filter this data.

7. Click the filter switch in the number column.
8. Choosenumber filterPossibility.
9. ChooseIs equal toPossibility.

10. Enter 1 inchfilter rowsMenu.
11. PressOKTaste.

Now you can count the results.

12. Go totransformBody.
13. 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.

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 itApplication.Sheet-Funktionaccess 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 elementsunique numberincreases 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 calledall elements.

Then all elements in the file are searchedall elementsArray and use the method to check if it is a duplicate elementIndexMethod.

TheIndexThe 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, thenIndexreturns -1 and the item is unique!

The unique elements are then added to another array namedunique items.

Number of items inunique itemsThe 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.

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!