Count unique values in Excel

How many times does each value occur in a column, and how many unique values are there at all? This is one of the first questions about any list: orders by customer, keywords by group, products by category. Excel gives several answers, from a single formula to a pivot table.

Pivot tables are a great tool with huge possibilities for data analysis: you can look at data from different angles, filter it and create calculated fields. But anyone who works with them knows their problems:

• a pivot table is a query that has to be refreshed, and on large datasets it takes a while to build;
• you can’t refer to pivot data by simply clicking cells: GETPIVOTDATA appears instead of a normal reference;
• it’s a separate object, not ordinary rows and columns, so to get a final report you need a few more steps.

What if the task is simple: count the unique cells in a range and list them, or make a quick summary by one or two columns, just to take a look and make a quick decision? Here are all the ways, from formulas to one click.

Count unique values with a formula

To get just the number of unique values in a range, in Excel 365 combine UNIQUE and COUNTA:

=COUNTA(UNIQUE(A2:A100))

In older versions, the classic formula works. COUNTIF counts how many times each value occurs, 1 divided by this count gives fractions that add up to exactly 1 for each unique value, and SUMPRODUCT adds them:

=SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100))

Make sure the range has no empty cells: COUNTIF returns 0 for them, and the formula returns #DIV/0!. If there are blanks, use =SUMPRODUCT((A2:A100<>“”)/COUNTIF(A2:A100,A2:A100&””)).

How to count cells with the same value

To see how many times a particular value occurs, put this formula next to the column and fill it down:

=COUNTIF($A$2:$A$100,A2)

Each row gets the number of occurrences of its value. The downside is that repeated values get the same number many times, so you’ll still need to remove duplicates to get a clean list.

In Excel 365, one formula builds the whole report: the list of unique values with their counts next to them:

=LET(u,UNIQUE(A2:A100),HSTACK(u,COUNTIF(A2:A100,u)))

And in the latest Excel 365 versions, the GROUPBY function does the same and can also sort the result:

=GROUPBY(A2:A100,A2:A100,COUNTA)

These are dynamic array formulas: they spill the result into as many rows as needed.

Count unique values with a pivot table

• Select the column with its header and go to Insert → PivotTable → OK.
• Drag the field to Rows, and then drag the same field to Values. Excel switches it to Count automatically for text.
• To count distinct values in a pivot, check Add this data to the Data Model when creating it, and choose Distinct Count in Value Field Settings.

It works well for a report you’ll update, but it’s a lot of clicks for a quick look.

Count repeated cells with !SEMTools

That’s why the Fast pivot table tool was born in !SEMTools. It does just one thing, a summary with counting and summing, but does it as fast as possible, without asking unnecessary questions.

In the example, the cells contain one letter each, but in reality it can be any repeated data. Select the column and click the procedure: it groups identical cells and outputs their counts to a separate sheet. No formulas, no pivot settings:

Merge identical cells and sum the other columns

When you need a quick summary with the sums of certain metrics, for example clicks and costs by keyword, select the columns and run the same procedure. There can be any number of metric columns. Empty and text values among the numbers are skipped automatically.

Important: the procedure groups by the leftmost column of the selected range and sums all the columns to the right of it, so you may need to arrange the data accordingly. It also doesn’t keep the source formatting, for the sake of speed on large datasets.

Common mistakes

• The same values are counted separately. They differ in extra spaces, case or invisible characters. Remove extra spaces first. Phrases with a different word order are implicit duplicates and need normalization.
• The classic formula returns #DIV/0!. The range contains empty cells: use the version with &”” shown above.
• The pivot table shows Sum instead of Count. The column contains numbers: change the field to Count in Value Field Settings.

Summary

I hope you found what you were looking for on this page and were able to count the unique values in your data. For a single number, COUNTA(UNIQUE) or SUMPRODUCT(1/COUNTIF) is enough. For a report with counts, use UNIQUE with COUNTIF or GROUPBY in Excel 365, or a pivot table. For a quick look at any list, the !SEMTools fast pivot does it in one click.

You may also be interested in how to find duplicates in Excel and in other statistical metrics: occurrence numbers, shares and ABC analysis.