Sort, Unique, Filter in Excel With Multiple Criteria

Formulas & functions · Updated November 20, 2026

In Excel for Microsoft 365, combine FILTER, UNIQUE, and SORT to return sorted distinct results that meet multiple criteria. For example, use =SORT(UNIQUE(FILTER(B2:B100,(A2:A100=E2)*(C2:C100=E3),""))) to list each qualifying salesperson once, in alphabetical order, when both the selected region and product match.

What the three functions do

The functions have separate jobs, so their order matters. FILTER keeps rows that satisfy a condition; UNIQUE removes repeated values or rows from that result; and SORT arranges what remains. Nesting them in that order makes the logic readable: first decide which records count, then remove duplicates, then sort the output.

These are dynamic-array functions in Microsoft 365 and Excel 2021 or later. The formula goes in one cell, and Excel spills the result into nearby cells. Keep the intended spill area empty. The formulas below use commas between arguments, as in the standard English-language Excel syntax; some regional settings display a different argument separator.

Prepare the source data and criteria

For the examples, assume a worksheet has headers in row 1 and records in rows 2 through 100: column A contains Region, B contains Salesperson, C contains Product, and D contains Date. Put a region to match in E2 and a product to match in E3. The criteria cells can contain values selected from lists or typed directly, but their values need to match the source data.

Before writing a formula, check that each field is in a consistent format. For instance, a date stored as text may not compare as expected with a real Excel date, and a trailing space in a product name can make it look identical while behaving differently. Use a table with one header row and no merged cells within the data if the source is likely to grow. Fixed ranges such as rows 2 through 100 will not include new records added below row 100 unless you extend the references.

Return unique values that meet AND criteria

In the opening formula, the two tests are multiplied: (A2:A100=E2)*(C2:C100=E3). Excel evaluates each comparison across the rows. A row is included only when both comparisons are TRUE, because TRUE behaves like 1 and FALSE like 0 in this arithmetic. For example, a West record for Desks qualifies when E2 is West and E3 is Desks; an East record or a West record for Chairs does not.

With the stated layout and criteria cells, the formula returns the distinct salesperson names for records matching both the selected region and product. If the qualifying names are Mia, Omar, and Mia in the source, the result is Mia and Omar, sorted alphabetically. If you instead want distinct combinations of fields, filter the full columns for those fields rather than only the salesperson column.

For example, with the same data layout, E2 holding the region and E3 holding the product, this returns unique Region–Salesperson–Product rows matching both criteria, sorted by the first column:

=SORT(UNIQUE(FILTER(A2:C100,(A2:A100=E2)*(C2:C100=E3),"")))

The order of columns in the returned array follows the order in the range A:C. Since all returned rows meet the same region and product tests, repeated combinations collapse to one row, while different salesperson names remain separate combinations.

Use OR criteria or mix AND and OR

Multiplication represents AND. Addition can represent OR, but it is safer to test whether the sum is greater than zero. That way, a row matching both alternatives is still treated as one included record. For a salesperson list from either of two regions, put the two region choices in E2 and F2.

With Region in A2:A100, Salesperson in B2:B100, and the two region choices in E2 and F2, use:

=SORT(UNIQUE(FILTER(B2:B100,((A2:A100=E2)+(A2:A100=F2))>0,"")))

For mixed logic, group the OR tests in parentheses and multiply by the AND test. With region choices in E2 and F2 and a required product in G2, use this formula with the same source columns:

=SORT(UNIQUE(FILTER(B2:B100,((A2:A100=E2)+(A2:A100=F2))*(C2:C100=G2),"")))

This means “the region is E2 or F2, and the product is G2.” Parentheses are important: they make Excel evaluate the two-region choice as a group before combining it with the product condition. In Excel formulas, > is the greater-than operator; it is shown as an HTML entity here so it displays correctly.

Filter by dates, numbers, or more conditions

The same pattern works with dates and numeric tests. Use comparison operators such as greater than or equal to, and multiply each condition when all of them must be true. For instance, a date interval can be combined with a region condition. Enter real Excel dates in the criteria cells rather than date-looking text.

With Region in A2:A100, Salesperson in B2:B100, Date in D2:D100, the desired region in E2, and inclusive start and end dates in F2 and G2, use:

=SORT(UNIQUE(FILTER(B2:B100,(A2:A100=E2)*(D2:D100>=F2)*(D2:D100<=G2),"")))

This returns each salesperson with at least one record in that region during the selected date interval. The distinct list does not count how many matching records each person has; it only identifies names that appear. If the task is to calculate totals or counts by salesperson, use a PivotTable or a separate aggregation formula instead.

Choose what to sort and how

By default, SORT sorts ascending by the first column of its array. A one-column name list therefore sorts alphabetically, while a multi-column result sorts by its leftmost returned field. You can change the sort column or direction using the optional arguments of SORT.

With the same A:C source data, E2 as region, and E3 as product, this returns unique matching rows and sorts them by the second returned column, Salesperson, in ascending order:

=SORT(UNIQUE(FILTER(A2:C100,(A2:A100=E2)*(C2:C100=E3),"")),2,1)

In this function, the second argument identifies the sort index and the third sets the order: 1 is ascending and -1 is descending. The index is relative to the returned array, not necessarily the worksheet column number. If your returned array is B:D, for example, its second column is worksheet column C. To sort unique names by a related sales total, first decide how that total should be calculated for each name; a simple sort of the unique names cannot infer which source row or total should control their order.

Handle no matches, blanks, and spill errors

The optional third argument in FILTER supplies a value when no rows match. In the examples, "" displays a blank rather than the #CALC! error that FILTER otherwise returns for an empty result. That placeholder can flow through UNIQUE and SORT as a blank-looking output, so it is not always the same as returning a truly empty array. If you want a clear message instead, change the placeholder to text such as "No matches".

Blank source cells can also appear in a result if they meet the filter conditions. To exclude blank names, add a nonblank test. With Salesperson in B2:B100 and the earlier Region and Product criteria in E2 and E3, use:

=SORT(UNIQUE(FILTER(B2:B100,(A2:A100=E2)*(C2:C100=E3)*(B2:B100<>""),"No matches")))

If Excel shows #SPILL!, select the formula cell and inspect the cells where the result should expand. A value, another formula, or a merged cell in the spill range can block the output. Clear the obstructing cells or move the formula. Also check that the result is not being entered inside an Excel Table: dynamic-array formulas spill outside tables, so place the formula in the worksheet grid beyond the table instead.

Common mistakes and useful alternatives

  • Using the wrong logic: Multiplication requires every test to pass; addition with a greater-than-zero test accepts either condition. Translate the request into AND and OR before building the formula.
  • Unequal range sizes: The return range and every condition range should cover the same rows. A range ending at row 100 should not be paired with a condition ending at row 99.
  • Expecting case-sensitive matches: Ordinary equality tests in this pattern are not case-sensitive. If capitalization must distinguish values, use a case-sensitive approach such as EXACT within a suitable array formula.
  • Expecting a normal filter dropdown to create a distinct list: AutoFilter hides rows that fail conditions; it does not produce a separate deduplicated, sorted output. The formula method creates a live result elsewhere on the sheet.

For a one-time extraction or users without dynamic-array functions, Excel also has Data > Advanced for Advanced Filter. It can copy filtered unique records to another location, but it is a manual operation that generally must be run again after the source or criteria change. Remove Duplicates is another built-in option, but it changes the selected data rather than maintaining a separate formula-driven result. A PivotTable can be better for grouped summaries, counts, and totals; it is not a direct replacement when the goal is simply a live sorted list of distinct values.

Frequently Asked Questions

Can I use SORT, UNIQUE, and FILTER in older Excel?
They are available in Microsoft 365 and Excel 2021 or later. Earlier perpetual versions may not support these functions. In those versions, consider Advanced Filter for a manual unique extraction, or a PivotTable for grouped analysis.

Does UNIQUE remove duplicate rows or just duplicate values?
It depends on the array you pass to it. A single-column range produces distinct values from that column. A multi-column range produces distinct rows based on the combination of values across those columns.

How do I make the result update when I add records?
Convert the source range to an Excel Table using Insert > Table, or extend the formula ranges to cover future rows. Tables expand as records are added, but a dynamic-array formula that refers to table columns should be entered outside the table because its result needs room to spill.

Can I sort the result in descending order?
Yes. Set the third argument of SORT to -1. For a one-column result, that reverses the usual ascending order; for a multi-column result, specify the sort index as well if you want to sort by a column other than the first.

Why does my formula return a blank when I expected “no results”?
The examples use an empty-string fallback in FILTER, which creates a blank-looking result when nothing qualifies. Replace that fallback with a message such as "No matches" if you want the sheet to make the empty-result case explicit.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.