How to Count Unique Values in Excel, and Get a Distinct Count in a Pivot Table

Count unique values in Excel with UNIQUE or SUMPRODUCT, add a condition, turn on Distinct Count in a pivot table, and avoid adding monthly unique counts.

Founder, Glow Reports. , 3 min read
  • Excel
  • Formulas
  • Pivot Tables
A messy pile of colored cards going through a funnel and coming out as a short, neat row of single cards
On this page
  1. Why does Excel count 8 when there are 5 customers?
  2. How do you count unique values in Excel 365?
  3. How do you count unique values in older versions of Excel?
  4. How do you count unique values with a condition?
  5. How do you get a distinct count in a pivot table?
  6. How do you count unique values in Google Sheets?
  7. Where does Glow Reports fit?
  8. Common questions

You want the number of customers who ordered last month, so you build a pivot table and drop the customer column into Values. It shows 8. You know there were 5. Count counts rows, and a customer who ordered three times counts three times. In Excel 365, returns the number of unique values in one cell. Below: that formula, the one for older versions, a unique count with a condition, the Distinct Count option hidden in pivot tables, and Google Sheets.

Why does Excel count 8 when there are 5 customers?

COUNTA and the Count summary of a pivot table count cells, so every repeat of a value counts again. To count each value once, you need a formula that removes the duplicates first, or a pivot table built on the Data Model.

The examples below use an order export with eight rows: the order number in column A, the customer ID in column B and the month in column C.

OrderCustomerMonth
1001C01Jan
1002C02Jan
1003C01Jan
1004C03Feb
1005C02Feb
1006C01Feb
1007C04Feb
1008C05Mar

Eight orders, five customers. C01 ordered three times, C02 twice, and three customers once each. Microsoft's own page states the goal the same way: in a column holding 5, 6, 7 and 6, "the result is three unique values" Microsoft Support.

How do you count unique values in Excel 365?

Wrap UNIQUE inside COUNTA. UNIQUE returns the list without duplicates, and COUNTA counts that list. With customer IDs in B2:B9, returns 5.

Unique customers, 5
=COUNTA(UNIQUE(B2:B9))

UNIQUE "returns a list of unique values in a list or range" Microsoft Support. It works in Excel for Microsoft 365, Excel 2021 and Excel 2024, and on the web. Excel 2019 and older return #NAME?, so use the formula in the next section there.

Blank cells need care. UNIQUE keeps one blank in its list and COUNTA counts it, so an export with empty customer cells comes out one too high. Filter the blanks out first:

Unique customers, blanks excluded
=COUNTA(UNIQUE(FILTER(B2:B9,B2:B9<>"")))

UNIQUE has a third argument. With set to TRUE, it returns only the values that appear once, which Microsoft describes as "the database concept of unique". returns 3: the customers who ordered a single time. That figure is the start of a repeat purchase analysis, covered in how to calculate customer retention rate.

How do you count unique values in older versions of Excel?

Divide 1 by the number of times each value appears, and add the results. With SUMPRODUCT and COUNTIF, returns 5 in any version of Excel.

Unique customers in any version, 5
=SUMPRODUCT(1/COUNTIF(B2:B9,B2:B9))

COUNTIF returns, for each row, how many times its customer appears: 3 for each C01 row, 2 for each C02 row, 1 for the others. One divided by those counts gives 1/3 three times for C01, 1/2 twice for C02 and 1 for each of the rest. Each customer adds up to exactly 1, so the total is the number of customers.

A blank cell makes COUNTIF return 0 and the formula fails with #DIV/0!. This version skips blanks:

Unique customers in any version, blanks excluded
=SUMPRODUCT((B2:B9<>"")/COUNTIF(B2:B9,B2:B9&""))

The formula compares every row with every other row, so it slows down on a large export. Microsoft documents two other routes, one with FREQUENCY and MATCH and a manual one: DataSort & FilterAdvanced, then "Unique records only" to copy the distinct values elsewhere and count them with ROWS Microsoft Support.

Both formulas treat "C01" and "C01 " with a trailing space as two customers. Google's help for its own UNIQUE function gives the same warning about "hidden text such as trailing spaces" Google Docs Editors Help. Run TRIM on the column first, as shown in how to clean data in Excel.

How do you count unique values with a condition?

Filter the rows before UNIQUE counts them. For February, returns 4. In older versions, returns the same 4.

Unique customers in February, 4
=COUNTA(UNIQUE(FILTER(B2:B9,C2:C9="Feb")))

FILTER keeps the rows where the test is true Microsoft Support. For two conditions, multiply the tests: . Put the month in a cell, such as F2, and write so the formula copies down a list of months.

The monthly counts give 2 customers in January, 4 in February and 1 in March. They add up to 7, while the quarter had 5 customers. C01 and C02 ordered in more than one month, so they appear in more than one count.

7 is the sum of the three monthly unique counts. The quarter had 5 unique customers.

Never add unique counts across periods or groups to get a total. Compute the total on its own, over the whole range.

How do you get a distinct count in a pivot table?

Tick "Add this data to the Data Model" when you create the pivot table. The Distinct Count summary then appears in the value field settings. Without the Data Model, Excel offers Count, which counts rows.

  1. Select a cell in the data and choose InsertPivotTable.
  2. In the dialog, tick "Add this data to the Data Model" and select OK Microsoft Support.
  3. Drag Month to Rows and Customer to Values.
  4. Right-click a number in the Values area and choose Summarize Values ByMore Options.
  5. Scroll to the bottom of the list and pick Distinct Count.

A Microsoft answer on the topic puts it plainly: "The Distinct Count aggregation function is only available if the Power Pivot Data Model is behind the pivot table" Microsoft Q&A. A pivot table already built without the box can't switch. Build a new one from the same range with the box ticked.

The pivot table shows 2, 4 and 1 for the three months, and 5 on the grand total row. The Data Model computes the total on its own, so it avoids the trap of adding monthly counts. For grouping order dates into months first, see how to create a pivot table by month. When the counts don't move after new rows arrive, the pivot table needs a refresh, as explained in how to refresh a pivot table.

How do you count unique values in Google Sheets?

Use COUNTUNIQUE. With customer IDs in B2:B9, returns 5. Google's help describes it as counting "the number of unique values in a list of specified values and ranges" Google Docs Editors Help.

For a condition, combine it with FILTER: returns 4. Sheets also accepts the UNIQUE formulas above.

Where does Glow Reports fit?

The formulas above answer one question on one file. A monthly report asks several: unique customers by month, by country, new against returning, each from a fresh export with its own blanks and trailing spaces.

Glow Reports starts from that export. You upload the CSV or Excel file and ask, in plain language, for the number of unique customers by month. The AI writes the SQL, a grouped by month, builds the chart and drafts the text. The SQL sits under each figure, so you can check that the count is distinct and edit it to exclude test orders or blank IDs. Reports export to PDF. The free plan gives 300 credits once, and a report costs 100 credits. The same distinct count, on order IDs, is the bottom of the fraction in average order value.

Common questions

What is the difference between unique and distinct values?

In everyday use, both mean counting each value once: C01, C02, C03, C04 and C05 make 5 distinct customers. Excel's UNIQUE with set to TRUE uses "unique" in the stricter sense of values that appear once, which gives 3 in the example.

Why is Distinct Count missing from my pivot table?

The pivot table was created without the Data Model. Build a new one and tick "Add this data to the Data Model" in the creation dialog. Distinct Count then appears at the bottom of the summary list.

Why does COUNTA(UNIQUE()) return one more than expected?

The range holds a blank cell, and UNIQUE keeps one blank in its result. Use to leave blanks out.

Can I count unique values across two columns?

Yes. UNIQUE compares whole rows, so counts the unique pairs of customer and month. The example returns 7, one per customer per month they ordered in.

El Mehdi Benane
Founder, Glow Reports

I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.

Upload your order export to Glow Reports and get distinct counts by month or group, with the SQL behind each figure.

Keep reading