Count Distinct Customers per Month Both Ways in Excel

How many different customers bought something in March? It is a plain question, and a plain COUNT gets it wrong, because it counts orders, and a customer who bought four times counts four times. A distinct count fixes that, and the ways to make one in a spreadsheet are all slightly awkward. This article covers the Excel options, the SQL form, the handling of blanks and the common error of adding monthly distinct counts. It is one of the worked pairs in moving between Excel and SQL, and the same function appears in removing duplicates in Excel and SQL.

Sample data

Orders with a customer ID and an order date, in Orders and in a table orders(order_id, customer_id, ordered_on):

January has three orders and two distinct customers (10 and 11). February has three orders and three distinct customers (10, 12, 11). March has one order and one customer (12). Across the quarter there are three distinct customers, not the 2 + 3 + 1 = 6 you get by adding the months.

Excel 365: UNIQUE and FILTER

For one month, filter the rows, take the unique customers and count:

=COUNTA(UNIQUE(FILTER(Orders[customer_id],
        (Orders[ordered_on]>=DATE(2026,1,1)) * (Orders[ordered_on]<DATE(2026,2,1)),
        "")))

For January this returns 2. The third argument of FILTER, an empty string, stops an empty result from raising #CALC!; but then UNIQUE returns the empty string and COUNTA counts it as 1. Use IFERROR(..., 0) carefully, or count only numbers with COUNT if customer IDs are numeric:

=COUNT(UNIQUE(FILTER(Orders[customer_id], (Orders[ordered_on]>=F2)*(Orders[ordered_on]<EDATE(F2,1)), "")))

With the month's first day in F2, this returns the distinct count and 0 for an empty month, because COUNT ignores the text. Fill it down a column of month starts for the full table of counts.

For a spill of all months at once, build the month list and map over it:

=LET(m, SORT(UNIQUE(EOMONTH(Orders[ordered_on], -1) + 1)),
     n, MAP(m, LAMBDA(x, COUNT(UNIQUE(FILTER(Orders[customer_id],
          (Orders[ordered_on]>=x)*(Orders[ordered_on]<EDATE(x,1)), ""))))),
     HSTACK(m, n))

This returns two columns, month and distinct customers: 2, 3 and 1 for the sample. It needs a recent Microsoft 365 build.

Excel without dynamic arrays: SUMPRODUCT

A classic formula counts each customer once by dividing by how many times it appears in the month:

=SUMPRODUCT((Orders[ordered_on]>=F2) * (Orders[ordered_on]<EDATE(F2,1)) /
            COUNTIFS(Orders[customer_id], Orders[customer_id],
                     Orders[ordered_on], ">="&F2, Orders[ordered_on], "<"&EDATE(F2,1)))

For January, customer 10 has two orders in the month, so each of its rows contributes 1/2, summing to 1; customer 11 has one, contributing 1. The total is 2. The catch is the denominator: a row outside the month has a COUNTIFS of zero, and dividing by zero gives #DIV/0!. Guard it by adding a condition inside the divisor: COUNTIFS(...)+(Orders[ordered_on]<F2)+(Orders[ordered_on]>=EDATE(F2,1)), so the denominator is never zero for rows that the numerator zeroes out. The formula is quadratic in the number of rows and slow past about 10,000.

Excel PivotTable: Distinct Count

A pivot table can count distinct values if the source is added to the Data Model. Insert, PivotTable, tick "Add this data to the Data Model". Drag ordered_on into Rows and group by month, drag customer_id into Values, then Value Field Settings, Distinct Count. The pivot shows 2, 3 and 1. The Data Model stores a compressed copy of the data in the file, and the pivot needs Excel (not a script) to refresh (pivot tables and their SQL equivalents).

SQL: COUNT(DISTINCT)

SELECT date_trunc('month', ordered_on)::date AS month,
       count(*)                      AS orders,
       count(DISTINCT customer_id)   AS customers
FROM orders
GROUP BY 1
ORDER BY 1;

For the sample, the output is:

Showing orders beside customers makes the difference visible. For the quarter's total, run the distinct count over the whole range, not as the sum of the months:

SELECT count(DISTINCT customer_id) AS customers_in_quarter
FROM orders
WHERE ordered_on >= DATE '2026-01-01' AND ordered_on < DATE '2026-04-01';

This returns 3. Adding the monthly counts would give 6.

Blanks and NULLs

count(DISTINCT customer_id) skips NULLs, so an order with no customer is not a customer. The Excel formulas with COUNTA treat a blank key as a value if it survives the filter, which adds one. Decide whether anonymous orders count and handle them explicitly. In SQL, to count a missing customer as one group: count(DISTINCT coalesce(customer_id::text, 'unknown')). In Excel, FILTER(..., Orders[customer_id]<>"") removes blanks first.

Data type matters. The IDs 10 and "10" are different in UNIQUE when one is text and one is a number. A column mixing the two, typical after an import, counts the same customer twice (detecting mixed types in an Excel column). Normalize first.

The key you count by

A distinct count is only as good as the key. Counting by name or email treats "Acme" and "Acme Ltd" as two customers and merges two people who share an email. Use a stable identifier, ideally the database's customer ID, not something typed. If the only available key is messy, clean it before counting and say so: "Customers are distinct email addresses, lower-cased."

Why monthly counts do not add

Customer 10 is active in January and February. A monthly distinct count includes them in both. The sum of the months counts them twice. The quarter's distinct count includes them once. Reports that add monthly "active customers" into an annual figure inflate it, and the error grows with retention. If you need an annual number, count distinct over the year. If you need "new customers per month", count customers whose first order falls in the month, which uses min(ordered_on) per customer:

SELECT date_trunc('month', first_order)::date AS month, count(*) AS new_customers
FROM (SELECT customer_id, min(ordered_on) AS first_order FROM orders GROUP BY customer_id) f
GROUP BY 1 ORDER BY 1;

That sums to the total number of customers, because each is counted once.

Speed on large data

count(DISTINCT x) sorts or hashes the values, which is fine for millions of rows and heavy for billions. Databases offer approximate distinct counts (HyperLogLog-based, for example in BigQuery and Redshift, or the hll extension for PostgreSQL), which trade a small error for speed and are fine for dashboards and wrong for billing. In Excel, speed drops sharply with the SUMPRODUCT version and moderately with UNIQUE; for more than tens of thousands of rows, pre-aggregate in SQL and bring the monthly figures into the sheet.

Checking

For the sample, the answers are 2, 3 and 1 by month and 3 for the quarter. Compare the sheet's figures and the query's, then check that the quarterly distinct count is less than the sum of the months (6) whenever customers repeat. Look at a few customers by hand. Check that the number of rows with a blank key is known and decided.

When a distinct count is the wrong measure

A distinct count is the wrong measure when the question is about volume, which is orders or revenue, or about loyalty, which is repeat rate. It is wrong when customers change identity over time and the key does not follow. And it is wrong to present monthly distinct counts as a cumulative customer base. For what Excel offers beyond these formulas, Microsoft explains counting unique values, and the PostgreSQL manual lists aggregate functions including count(DISTINCT).