Count Unique Values With Criteria in Excel

To count unique values with criteria in Excel, filter the rows first, remove the duplicates, then count: =COUNTA(UNIQUE(FILTER(B2:B8,A2:A8="North"))). That answers questions like "how many different customers bought in the North?" This guide builds the formula step by step, adds more conditions, fixes the "1 instead of 0" problem, and shows a method for older Excel.

All the examples use this sales table in A1:C8:

North has four sales but only three different customers, because Acme bought twice. COUNTIF would say 4. The goal here is 3.

How to count unique values with one condition#

The formula nests three functions. Read it from the inside out:

=COUNTA(UNIQUE(FILTER(B2:B8,A2:A8="North")))

1. FILTER(B2:B8,A2:A8="North") keeps the customers on North rows: Acme, Cobalt, Acme, Echo.
2. UNIQUE(...) removes the repeat: Acme, Cobalt, Echo.
3. COUNTA(...) counts the names that are left: 3.

FILTER and UNIQUE need Excel 2021, Excel 2024 or Microsoft 365. If you are new to them, the UNIQUE function guide and the COUNTA function guide explain each part. To see the list itself rather than the count, drop COUNTA. The article on UNIQUE with criteria covers that version.

A good habit is to build the formula in stages. Type the FILTER part alone first and check the list it spills. Then wrap it in UNIQUE, then in COUNTA. If something goes wrong, you can see exactly which step caused it.

How to count unique values with multiple criteria#

For two or more conditions, put each one in brackets and multiply them inside FILTER. To count different North customers with a sale of 500 or more:

=COUNTA(UNIQUE(FILTER(B2:B8,(A2:A8="North")*(C2:C8>=500))))

Each condition gives TRUE or FALSE for every row. Multiplying turns them into 1s and 0s, and only rows where both are 1 are kept. North sales of 500 or more are Acme (500) and Echo (600), so the result is 2.

For OR logic, add the conditions instead. (A2:A8="North")+(A2:A8="East") keeps rows from either region, giving Acme, Cobalt, Echo and Dyna: 4. The guide to FILTER with multiple criteria explains the * and + rules in more detail.

Why the count shows 1 when nothing matches#

Try a region with no sales, such as West. FILTER finds no rows and returns a #CALC! error. If you give FILTER a third argument to avoid the error, like "", it returns one empty value, and COUNTA counts it. Either way you don't get the 0 you expect.

Use ROWS to count instead, and IFERROR to turn the error into 0:

=IFERROR(ROWS(UNIQUE(FILTER(B2:B8,A2:A8="West"))),0)

ROWS counts the rows in the unique list. When FILTER fails, IFERROR returns 0. For North, the same formula returns 3, so you can use this safer version everywhere.

How to leave out blank cells#

If a North row has no customer name, FILTER returns that empty cell as a 0, and UNIQUE keeps it as one more "value". Your count goes up by one. Add a condition that the customer is not blank:

=COUNTA(UNIQUE(FILTER(B2:B8,(A2:A8="North")*(B2:B8<>""))))

B2:B8<>"" is TRUE only for cells with something in them. UNIQUE is also not case-sensitive, so "acme" and "Acme" count as one customer, which is usually what you want.

How to build a unique count for each category#

To get the unique count for every region at once, list the regions and point the condition at a cell. Get the region list with =UNIQUE(A2:A8) in E2, which spills North, South and East. Then in F2:

=COUNTA(UNIQUE(FILTER($B$2:$B$8,$A$2:$A$8=E2)))

Fill down to F4. The results are 3 for North, 2 for South (Brio and Acme) and 1 for East. The $ signs keep the data ranges fixed as you fill. To show the regions in order, use the ideas from the sorted unique list guide.

How to count unique values with criteria in older Excel#

Excel 2019 and earlier have no FILTER or UNIQUE. Use SUMPRODUCT with COUNTIFS:

=SUMPRODUCT((A2:A8="North")/COUNTIFS(A2:A8,A2:A8,B2:B8,B2:B8))

Here is how it works. COUNTIFS counts how often each region-and-customer pair appears. North-Acme appears twice, so each of those rows is worth 1/2. Cobalt and Echo appear once, so each is worth 1. The (A2:A8="North") part sets every non-North row to 0. Adding up gives 1/2 + 1 + 1/2 + 1 = 3.

This method breaks if the customer column has blank cells in the rows you are counting, because a blank pair can give a divide-by-zero error. Clean the data first. For more unique-count methods without conditions, see how to count unique values in Excel.

Count unique values with a pivot table#

If you prefer clicks to formulas, a pivot table can do this in Excel for Windows. When you create the pivot table, tick Add this data to the Data Model. Put Region in Rows and Customer in Values. Then open Value Field Settings and choose Distinct Count. You get 3, 2 and 1 for the three regions, the same as the formulas.

Common mistakes#

• Using COUNTIF. It counts every matching row, so repeat customers are counted more than once.
• Mismatched ranges. The FILTER values and the condition must cover the same rows, such as B2:B8 and A2:A8.
• Missing brackets around each condition when you multiply them.
• Extra spaces make "Acme" and "Acme " look like two customers. Clean the column with TRIM first.

Practise it#

Practise the UNIQUE function in a live spreadsheet: you type each formula and it checks your answer instantly. Then practise FILTER to get fast at the conditions, and try the North customers exercise on this page.