Unique Values with Multiple Criteria in Excel

This article explains how to extract unique values from a list in Excel when you need to apply multiple criteria, such as filtering by category and date before listing the distinct items. You will learn the formulas and methods to achieve this in both older and newer versions of Excel, along with practical examples and common pitfalls.

Formula

The core of extracting unique values with multiple criteria is an array formula that combines the UNIQUE function (Excel 365/2021) with the FILTER function. The general formula is:

=UNIQUE(FILTER(range, (criteria_range1=criteria1) * (criteria_range2=criteria2)))

Where:

• range – the column or row that contains the values you want to list uniquely.
• criteria_range1, criteria_range2 – the columns or rows that hold the criteria to test.
• criteria1, criteria2 – the specific values or conditions you want to match.

The multiplication * acts as an AND operator. Each comparison returns TRUE/FALSE, and multiplying them converts to 1/0, so only rows where all conditions are TRUE are passed to UNIQUE.

Worked Example

Suppose you have a sales table with columns: Product (A2:A20), Region (B2:B20), and Date (C2:C20). You want a list of unique products sold in the “West” region after January 1, 2024.

Using the default values from the calculator (10 rows, 5 columns – but here we use 19 rows of data), the formula would be:

=UNIQUE(FILTER(A2:A20, (B2:B20="West") * (C2:C20>=DATE(2024,1,1))))

Step-by-step:

1. Evaluate B2:B20="West" – returns an array of TRUE/FALSE.
2. Evaluate C2:C20>=DATE(2024,1,1) – returns another array of TRUE/FALSE.
3. Multiply the two arrays – TRUE*TRUE=1, otherwise 0.
4. FILTER extracts only the rows from A2:A20 where the product is 1.
5. UNIQUE removes duplicates from that filtered list.

If the data has 19 rows and the West region has 5 sales after Jan 1, with 3 distinct products, the result is those 3 product names.

When to Use This Method

Use this approach when you need a dynamic, automatically updating list of unique items that meet specific conditions. It is ideal for dashboards, reports, and data cleaning tasks. The method works best when:

• Your data is in a structured table or named range.
• You need the result to update when source data changes.
• You are using Excel 365, Excel 2021, or Excel Online (where UNIQUE and FILTER are available).

How to Choose Between UNIQUE+FILTER and Older Formulas

If you are using an older version of Excel (2019 or earlier), the UNIQUE and FILTER functions are not available. You have two main alternatives:

• Array formula with FREQUENCY and MATCH – a complex formula that works in all versions but requires Ctrl+Shift+Enter in older versions.
• Power Query – a more user-friendly way to filter and remove duplicates, but it creates a static table unless you refresh.

For most users on modern Excel, the UNIQUE+FILTER combination is simpler and more readable. For legacy versions, consider upgrading or using Power Query.

Common Problems and Solutions

• #CALC! error – This occurs when FILTER returns no results. Check your criteria and ensure data types match (e.g., dates are real dates, not text).
• #VALUE! error – Often caused by mismatched range sizes. Ensure all ranges have the same number of rows.
• Blank cells in the result – If your source has blank cells, UNIQUE may return a 0 or blank. Use =UNIQUE(FILTER(range, (criteria)*(range<>""))) to exclude blanks.
• Case sensitivity – By default, Excel is case-insensitive. If you need case-sensitive matching, use EXACT within the criteria.
• Performance issues – On very large datasets (100k+ rows), array formulas can slow down. Consider using Power Query or a pivot table instead.

Alternative Methods

Besides the UNIQUE+FILTER formula, there are other ways to get unique values with criteria:

• PivotTable – Add the criteria fields as filters and the value field as rows; check “Add this data to the Data Model” and use “Distinct count” for unique values.
• Power Query – Use “Remove Duplicates” after filtering rows. This is efficient for large data and allows easy refresh.
• Advanced Filter – A built-in feature that can copy unique rows to another location, but it is static and less flexible.
• Array formula with INDEX/MATCH – For older Excel versions, a CSE formula can be used, but it is complicated and error-prone.

Each method has trade-offs in terms of simplicity, performance, and dynamic updating.

Frequently Asked Questions

Can I use OR logic instead of AND with multiple criteria?

Yes, replace the multiplication * with addition + in the FILTER criteria. For example, FILTER(range, (range1="A")+(range2="B")) returns rows where either condition is true. Be careful with parentheses to ensure correct precedence.

Why does my formula return a 0 instead of a blank?

This often happens when the source range contains empty cells. The UNIQUE function treats blanks as a value (0). To remove blanks, add a condition like (range<>"") inside the FILTER.

How do I extract unique values from multiple columns at once?

You can concatenate the columns into a single array using the ampersand operator, then apply UNIQUE. For example, =UNIQUE(FILTER(A2:A20&"-"&B2:B20, criteria)) gives unique combinations. To split them back, use TEXTSPLIT or separate columns.

Does this formula work in Google Sheets?

Yes, Google Sheets has the same UNIQUE and FILTER functions, so the formula works identically. The syntax is the same, though the separator in some locales may be a semicolon.

What if my criteria are in another sheet?

You can reference other sheets directly in the formula, e.g., FILTER(Sheet2!A:A, Sheet2!B:B="West"). Just ensure the ranges are the same size.

Can I sort the unique list automatically?

Yes, wrap the formula with SORT: =SORT(UNIQUE(FILTER(...))). This will sort the result alphabetically or numerically.

Related

• 5 Overlooked OxygenOS Features on OnePlus Phones
• BitLocker Password Security Requirements
• Webhook Quotas: Keep Signed Capacity Separate?

in r | Rows | - | 10 | 1
in c | Columns | - | 5 | 1
out total | Total cells | - | r * c | 0
out unique | Max unique (approx) | - | r * c | 0