UNIQUE With Criteria: List Distinct Values That Match

UNIQUE lists each value once, but it has no criteria argument. To use UNIQUE with criteria in Excel, put FILTER inside it: FILTER keeps only the rows that match your condition, and UNIQUE removes the repeats from what is left. This guide shows the basic formula, then sorting, multiple conditions, counting and what to do in older versions of Excel.

The sample data#

Every example uses this order list in A1:C8:

Customers appear more than once because each row is an order. The question is always the same: which different customers match the condition?

UNIQUE, FILTER and SORT are dynamic array functions. They need Excel 2021, Excel 2024 or Microsoft 365, and their results spill, which means they fill as many cells below as they need.

How to use UNIQUE with criteria#

Put the condition in FILTER and wrap FILTER in UNIQUE. To list the customers in the North region, click E2 and type:

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

The result spills into two cells: Acme and Cedar. Here is what happens, from the inside out:

1. B2:B8="North" checks each row and gives TRUE or FALSE.
2. FILTER keeps the customers from the TRUE rows: Acme, Acme, Cedar, Cedar.
3. UNIQUE removes the repeats: Acme, Cedar.

The FILTER function guide and the UNIQUE function guide cover each function's arguments on its own.

To make the condition easy to change, type the region in a cell, such as G1, and point at it: =UNIQUE(FILTER(A2:A8,B2:B8=G1)). Type South in G1 and the list changes to Birch and Delta.

How to sort the unique list#

Wrap the whole formula in SORT to get the values in A to Z order. To list every customer who bought Paper:

=SORT(UNIQUE(FILTER(A2:A8,C2:C8="Paper")))

The result is Acme, Birch, Cedar. Without SORT, UNIQUE keeps the order in which the values first appear, which here would be Acme, Cedar, Birch. For Z to A, use SORT(...,,-1), with -1 as the third argument.

How to use UNIQUE with multiple criteria#

Combine conditions inside FILTER: multiply them for AND, add them for OR. Each condition goes in its own brackets.

North customers who bought Toner (AND):

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

The result is Cedar, the only row that matches both.

Customers in the South or who bought Toner (OR):

=UNIQUE(FILTER(A2:A8,(B2:B8="South")+(C2:C8="Toner")))

FILTER keeps rows 3, 6, 7 and 8, and UNIQUE returns Birch, Delta and Cedar. The article on FILTER with multiple criteria explains why * means AND and + means OR, and why the AND() and OR() functions don't work here. The same pattern works with date conditions, as shown in how to filter by date.

Criteria that match part of the text#

FILTER has no wildcards, so use SEARCH for "contains" conditions. To list customers whose product contains "ink":

=UNIQUE(FILTER(A2:A8,ISNUMBER(SEARCH("ink",C2:C8))))

SEARCH returns a position when it finds the text and an error when it doesn't, and ISNUMBER turns that into TRUE or FALSE. The result is Birch and Acme. SEARCH ignores capitals; use FIND for a case-sensitive match. More patterns are in FILTER contains text.

How to count unique values with criteria#

Wrap the formula in ROWS to count how many distinct values match. To count the different South customers:

=ROWS(UNIQUE(FILTER(A2:A8,B2:B8="South")))

The result is 2 (Birch and Delta). COUNTA instead of ROWS gives the same answer.

Watch out when nothing matches. FILTER then returns a #CALC! error, and the count shows an error too. Wrap it so the answer is 0:

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

Don't solve this with FILTER's third argument, such as "None". ROWS would count "None" as one row and return 1. For more ways to count distinct values, including with several conditions, see how to count unique values with criteria.

How to return unique rows from several columns#

Give FILTER more than one column and UNIQUE removes duplicate rows, not just duplicate values. To see which customer and region pairs bought Paper:

=UNIQUE(FILTER(A2:B8,C2:C8="Paper"))

The result spills into two columns and three rows: Acme / North, Cedar / North and Birch / South. A row is only removed when every column matches another row.

If the data has empty cells, FILTER can return them as 0. Add a condition to skip them: (B2:B8="North")*(A2:A8<>"").

Why does UNIQUE with FILTER show an error?#

Each error points to one specific cause:

• #SPILL!: the cells where the list should spill aren't empty. Clear the cells below the formula.
• #CALC!: no rows match the condition. Check the spelling and spaces in the data, or add an IFERROR.
• #NAME?: your version of Excel doesn't have UNIQUE or FILTER. Use one of the methods below.
• #VALUE!: the ranges are different sizes, such as A2:A8 and B2:B9. Make them match.

In Excel 2019 and older, use a pivot table: put the criteria field (Region) in Filters and the value field (Customer) in Rows, and each customer appears once. You can also use Data > Advanced with Unique records only and a small criteria range. Neither updates by itself, so refresh or rerun it when the data changes. If you need totals rather than a list, the article on filtering and summing in one formula shows the next step.

Practise it#

FILTER and UNIQUE are quick to learn and save a lot of copying and pasting. Practise UNIQUE in a live spreadsheet: you build distinct lists and the lesson checks each formula instantly. Then practise the FILTER function with one and several conditions.