Function Problems

How to Count Distinct Values by Criteria in Excel

Steve K Sep 25, 2026 869 views

Question details

The user needs to find the number of unique items that match specific criteria in a dataset.

How to Count Distinct Values by Criteria in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Analyzing a dataset where the user must determine how many distinct items (like products or IDs) are associated with a specific category (like a salesperson or region).
Observed behavior: Requires a functional method or formula to accurately extract and count only the distinct values matching a specified condition.

Before you start

Ensure your dataset does not contain unintended trailing spaces in the text columns, and verify that your spreadsheet software supports dynamic array functions if you plan to use the UNIQUE and FILTER method.

Solution 1Recommended

Use Dynamic Array Formulas (UNIQUE, FILTER, COUNTA)

Combine Excel's newest dynamic array functions to quickly filter data by a condition and count the remaining unique values.

This method is highly efficient but requires a modern version of Excel (Microsoft 365) or WPS Office that supports dynamic arrays.

Prepare the result cell

Select the cell where you want the distinct count to appear, such as E2 next to your criteria cell (D2).

Enter the combination formula

Type the formula =COUNTA(UNIQUE(FILTER($B$2:$B$10,$A$2:$A$10=D2))) into the cell. This assumes column A holds your criteria and column B holds the values.

Apply to multiple criteria

Press Enter to get the count. If you have a list of criteria, drag the fill handle down to apply the formula to the remaining rows.

Alternative spilling formula

If you want the formula to spill automatically for all criteria in D2:D3, use the LAMBDA function: =BYROW(D2:D3,LAMBDA(r,COUNTA(UNIQUE(FILTER(B2:B10,A2:A10=r)))))

Use Dynamic Array Formulas (UNIQUE, FILTER, COUNTA)

Formula Breakdown: The FILTER function narrows the list down to the specific criteria, UNIQUE removes duplicates from that narrowed list, and COUNTA counts the remaining items.

Solution 2

Use a PivotTable with the Data Model

Leverage the Data Model in Excel to access the Distinct Count summarization option within PivotTables without writing complex formulas.

Solution 3

Calculate Using Power Query

An excellent method for large datasets or recurring reports, utilizing Power Query's built-in grouping functions.

Easily Count Distinct Values with WPS Spreadsheet

WPS Spreadsheet provides a powerful, fast, and familiar environment for data analysis. It fully supports dynamic array functions like UNIQUE and FILTER, making complex counts effortless.

1. Open your data file: Launch WPS Spreadsheet and open the file containing your dataset.
2. Select the target cell: Click the blank cell where you want the distinct count result to be displayed.
3. Input the dynamic formula: Type =COUNTA(UNIQUE(FILTER(B:B, A:A=D2))) replacing the column references with your actual data ranges.
4. Calculate the result: Press Enter to instantly view the unique count, and drag the formula down if you have multiple criteria.

Fully compatible with Microsoft Excel formulas and .xlsx formats.Built-in support for advanced dynamic array functions like UNIQUE, FILTER, and BYROW.Lightweight installation and lightning-fast processing for large datasets.Completely free to use for everyday data analysis and reporting tasks.

Frequently Asked Questions

Why does my UNIQUE and FILTER formula return a #CALC! error?

This happens when the FILTER function finds no data that matches your criteria, resulting in an empty array. You can fix this by adding the [if_empty] argument to the FILTER function, like this: =COUNTA(UNIQUE(FILTER(B2:B10, A2:A10=D2, ""))).

Can I count distinct values using standard COUNTIFS?

The COUNTIFS function natively counts all occurrences that meet a criteria, not just unique ones. To count distinct values using older functions, you would need a complex combination like =SUM(--(FREQUENCY(IF(A2:A10=D2, MATCH(B2:B10, B2:B10, 0)), ROW(B2:B10)-ROW(B2)+1)>0)), entered as an array formula (Ctrl+Shift+Enter).

Why is the Distinct Count option missing in my PivotTable?

The 'Distinct Count' option is an exclusive feature of the Excel Data Model. If you created a standard PivotTable without checking the 'Add this data to the Data Model' box during the initial creation step, the Distinct Count option will not appear in the Value Field Settings.

Excel & Data Problems