Function Problems

How to Count Unique Values with Criteria in Excel (Return Blank)

Nimra Malik Oct 10, 2026 869 views

Question details

The user needs an Excel formula to count unique values in one column based on a specific condition in another column, while returning a blank instead of an error when there are no matches.

How to Count Unique Values with Criteria in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Performing a conditional distinct count on a dataset without returning error values or triggering array spill errors.
Observed behavior: Standard IF array formulas cause a #SPILL! error when comparing entire ranges. The goal is to return a clean blank string when zero matches are found.

Before you start

Ensure you are using a modern version of Excel (Microsoft 365 or 2021) or WPS Office Spreadsheet, as this solution relies on Dynamic Array functions like UNIQUE and FILTER.

Solution 1Recommended

Use IFERROR, COUNTA, UNIQUE, and FILTER Functions

Combine dynamic array functions to filter your data, extract unique items, and count them, while safely handling no-match scenarios.

By using the FILTER function instead of a standard IF array, you avoid #SPILL! errors caused by Excel trying to output an array of results into a single cell.

Select the target cell

Click on the cell in your worksheet where you want the conditional unique count result to appear.

Apply the FILTER function

Type =FILTER(B:B, A:A=E3) to extract values from column B where the data in column A perfectly matches the criteria defined in cell E3.

Nest inside UNIQUE and COUNTA

Wrap your filter function to count only distinct values by typing: COUNTA(UNIQUE(FILTER(B:B, A:A=E3))).

Add IFERROR to handle blanks

To return a blank when no matches exist, enclose the entire formula in IFERROR like this: =IFERROR(COUNTA(UNIQUE(FILTER(B:B, A:A=E3))), ""). Press Enter to apply the formula.

Use IFERROR, COUNTA, UNIQUE, and FILTER Functions

Why this works: If the FILTER function finds no matching data, it throws a #CALC! error. IFERROR instantly intercepts this and replaces it with a clean blank output.

Advanced Spreadsheet Data Analysis

Count Unique Values Seamlessly with WPS Spreadsheet

WPS Spreadsheet fully supports dynamic array functions like UNIQUE and FILTER, allowing you to easily calculate complex conditional counts and analyze data for free.

1. Download and Open: Install WPS Office for free and open your spreadsheet document in the WPS Spreadsheet application.
2. Select Your Cell: Click the specific cell where you want to display the unique count result.
3. Enter the Formula: Input the exact same dynamic array formula: =IFERROR(COUNTA(UNIQUE(FILTER(B:B, A:A=E3))), "").
4. Press Enter: Hit Enter to instantly calculate the conditional unique values with zero compatibility issues.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Supports modern dynamic array functions including UNIQUE, FILTER, and SORT.Lightweight, fast, and free to use across Windows, Mac, and mobile devices.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my IF formula return a #SPILL! error?

Using an IF statement to compare an entire column range (like IF(A:A=E3, B:B)) generates an array of results. If you don't wrap it in a proper aggregating function, Excel attempts to display all results in adjacent cells, causing a #SPILL! error if space is blocked.

Can I count unique values with multiple criteria?

Yes, you can add multiple conditions within the FILTER function by multiplying them. For example, use =IFERROR(COUNTA(UNIQUE(FILTER(B:B, (A:A=E3)*(C:C="Yes")))), "") to filter values that match both conditions simultaneously.

Does this dynamic array formula work in older versions of Excel?

No, functions like UNIQUE and FILTER were introduced in Microsoft 365 and Excel 2021. For older versions like Excel 2016, you would need complex array formulas using SUM, IF, and FREQUENCY entered with Ctrl+Shift+Enter.

Why use IFERROR instead of IF to check for blanks?

The FILTER function natively returns a #CALC! error when it finds no matching records. Wrapping the entire formula in IFERROR is the most efficient way to catch this error natively and output a blank string ("") instead.

Excel & Data Problems