Function Problems

How to Count Unique Values with Multiple Criteria in Excel

Ayan Masood Oct 15, 2026 869 views

Question details

The user needs a formula to count unique order numbers for a specific channel, ensuring duplicate order numbers are ignored and only positive order values are counted.

How to Count Unique Values Based on Multiple Criteria in Excel

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Analyzing a sales or order worksheet where multiple rows may contain the same order number, requiring a distinct count filtered by a specific channel (Column A) and positive values (Column C).
Observed behavior: A dynamic array formula combining multiple conditions is needed to successfully filter the data and extract a distinct count without relying on complex pivot tables.

Before you start

Ensure your spreadsheet software supports modern dynamic array functions like UNIQUE and FILTER, which are available in Microsoft 365 and recent versions of WPS Office.

Solution 1Recommended

Use the COUNTA, UNIQUE, and FILTER Dynamic Array Formula

Combine Excel's modern dynamic array functions to seamlessly filter your data by multiple conditions and count the distinct distinct values.

This approach uses the FILTER function to isolate rows that match your criteria, the UNIQUE function to strip out duplicate order numbers, and the COUNTA function to tally the remaining unique records.

Define your FILTER criteria

To filter for a specific channel in column A (matching cell E2) and positive orders in column C, use the multiplication operator (*) for AND logic: FILTER($B$2:$B$1000, ($A$2:$A$1000=E2)*($C$2:$C$1000>0)).

Wrap with the UNIQUE function

Place the FILTER formula inside the UNIQUE function to ensure duplicate order numbers from column B are only listed once: UNIQUE(FILTER($B$2:$B$1000, ($A$2:$A$1000=E2)*($C$2:$C$1000>0))).

Count the results

Wrap the entire expression in the COUNTA function to count the number of unique items returned: =COUNTA(UNIQUE(FILTER($B$2:$B$1000,($A$2:$A$1000=E2)*($C$2:$C$1000>0)))).

Add error handling

If no orders match the criteria, FILTER returns an error, which COUNTA will incorrectly count as 1. Wrap the formula in IFERROR to handle this: =IFERROR(COUNTA(UNIQUE(FILTER($B$2:$B$1000,($A$2:$A$1000=E2)*($C$2:$C$1000>0)))), 0).

Use the COUNTA, UNIQUE, and FILTER Dynamic Array Formula

Dynamic Array Benefits: Because these are dynamic arrays, you simply press Enter. You do not need to use Ctrl+Shift+Enter as was required for legacy array formulas.

Easily Process Complex Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports modern dynamic array functions like UNIQUE and FILTER, allowing you to perform advanced data analysis and distinct counting with ease.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the order data.
2. Select Target Cell: Click on the cell where you want the final unique count result to be displayed.
3. Enter the Formula: Type the formula combining COUNTA, UNIQUE, and FILTER to apply your specific channel and positive value criteria.
4. Get Results Instantly: Press Enter to instantly process the dynamic array and view your accurate unique order count.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Native support for modern dynamic array functions including UNIQUE, FILTER, and SORT.Free, lightweight, and fast alternative for robust data analysis.Familiar user interface ensuring seamless migration from other spreadsheet software.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my COUNTA formula return 1 when there are no matching records?

If the FILTER function finds no matches, it returns a #CALC! error. The COUNTA function treats this error as a single value and returns 1. To fix this, wrap your formula in an IFERROR function, or use the [if_empty] argument in the FILTER function to handle empty results.

Can I use COUNTIFS instead of UNIQUE and FILTER?

COUNTIFS is great for counting based on multiple criteria, but it cannot count unique (distinct) values natively. To count distinct values based on criteria, you must use UNIQUE and FILTER together, or rely on complex SUMPRODUCT array formulas.

How do I count unique values with criteria in older versions of Excel?

If your software does not support dynamic arrays (UNIQUE/FILTER), you can use an array formula: =SUM(--(FREQUENCY(IF(($A$2:$A$1000=E2)*($C$2:$C$1000>0), MATCH($B$2:$B$1000, $B$2:$B$1000, 0)), ROW($B$2:$B$1000)-ROW($B$2)+1)>0)). You must press Ctrl+Shift+Enter to evaluate it properly.

How do I add another condition to the FILTER function?

You can add more conditions by multiplying them inside the FILTER function's include argument. For example, to also only include orders from the year 2023 in column D, append *($D$2:$D$1000=2023) to the criteria array.

Excel & Data Problems