Function Problems

How to Count Unique Excel Values Based on a Condition

Aamir Naveed Akram Sep 28, 2026 870 views

Question details

The user needs to calculate the number of distinct items in one column but only when a specific condition (such as 'Y' or 'N') is met in a corresponding column.

How to Count Unique Excel Values Based on a Condition

Product: Excel
Device & OS: not provided
Scenario: Filtering and summarizing large datasets where duplicate entries exist, and an accurate distinct count is needed based on specific categorical criteria.
Observed behavior: To extract a clean count of unique items meeting a specific Y/N criteria without manually deduplicating the data or double-counting repeated entries.

Before you start

Ensure you are using a modern version of Excel or a compatible spreadsheet application that supports dynamic array functions, as older versions will return a #NAME? error.

Solution 1Recommended

Use Dynamic Array Formulas (FILTER, UNIQUE, COUNTA)

This is the most efficient and recommended method for modern spreadsheet software to count distinct items with conditions.

Dynamic array functions can process arrays of data seamlessly. By nesting FILTER inside UNIQUE, and wrapping it all in COUNTA, you can extract the exact number of distinct entries that match your criteria.

Select the destination cell

Click on the blank cell where you want the final unique count result to appear.

Input the nested formula

Type the formula =COUNTA(UNIQUE(FILTER(A2:A100,B2:B100="Y"))). Adjust the range A2:A100 to match the column containing your items, and B2:B100 to match your condition column.

Calculate the result

Press Enter. The formula will first filter column A for rows where column B is 'Y', then isolate the unique values, and finally count them.

Use Dynamic Array Formulas (FILTER, UNIQUE, COUNTA)

Using Entire Columns: For entire columns, you can use =COUNTA(UNIQUE(FILTER(A:A,B:B="Y"))), though referencing exact ranges is recommended for better calculation performance on large files.

Solution 2

Use a PivotTable with Distinct Count (Older Versions)

A reliable alternative for older spreadsheet versions that do not support the FILTER and UNIQUE array functions.

Efficient Data Analysis Tool

Process Complex Data Easily with WPS Spreadsheet

WPS Office provides full support for advanced dynamic array functions like UNIQUE, FILTER, and COUNTA. You can directly apply these formulas to instantly count conditional unique values without dealing with legacy workarounds.

1. Open your dataset: Launch WPS Spreadsheet and open your existing Excel workbook (.xlsx).
2. Enter the formula: Select a blank cell and type =COUNTA(UNIQUE(FILTER(A:A, B:B="Y"))).
3. View your unique count: Press Enter to instantly get the accurate count of unique items matching your condition.

Full compatibility with Microsoft Excel formulas, functions, and .xlsx formats.Native support for modern dynamic arrays like FILTER and UNIQUE.Lightweight, fast, and completely free to use for everyday data tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my unique count formula return a #NAME? error?

The #NAME? error occurs if you are using an older version of Excel or a spreadsheet program that does not support modern dynamic array functions like FILTER and UNIQUE. Upgrading to the latest WPS Office or a newer version of Microsoft Office will resolve this issue.

Can I count unique values based on multiple conditions at once?

Yes, you can add more conditions to the FILTER function by enclosing each condition in parentheses and multiplying them. For example: =COUNTA(UNIQUE(FILTER(A2:A100, (B2:B100="Y")*(C2:C100="Yes")))).

How do I prevent the formula from counting blank cells?

If there are empty cells in your filtered data, they might be counted as an empty string. You can exclude them by adding another condition to ignore blanks: =COUNTA(UNIQUE(FILTER(A2:A100, (B2:B100="Y")*(A2:A100<>"")))).

Excel & Data Problems