Count unique distinct values based on a condition

This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above demonstrates a formula in cell E12 that counts unique distinct items in column C based on a condition applied to column B.

Unique distinct values are all values except duplicates that are merged into one distinct value.

1. Count unique distinct values based on a condition

The image above shows a table in columns B and C. Column B contains names and column C contains products. How many unique distinct products did Salesperson Jennifer sell?

The blue arrows show unique distinct products based on salesperson "Jennifer", the total number matches the number in cell E4. The remaining highlighted records are only duplicate values.

Array formula in cell E2:

Back to top

1.1 How to create an array formula

1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
2. Press and hold Ctrl + Shift.
3. Press Enter once.
4. Release all keys.

Back to top

1.2 Explaining array formula in cell E2

You can follow along as I explain the formula, select cell E2. Go to tab "Formulas" on the ribbon, press with left mouse button on "Evaluate formula" button.

Press with mouse on "Evaluate" button shown in above image to move to next step.

Step 1 - Calculate unique distinct products that Jennifer sold

The COUNTIFS function calculates the number of cells across multiple ranges that equals all given conditions.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

1/(COUNTIFS($B$5:$B$26, "Jennifer", $C$5:$C$26, $C$5:$C$26))

becomes

1/{4; 2; 3; 4; 0; 3; 4; 3; 4; 3; 0; 3; 2; 3; 2; 2; 2; 4; 4; 4; 4; 3}

and returns

{0,25; 0,5; 0,333333333333333; 0,25; #DIV/0!; 0,333333333333333; 0,25; 0,333333333333333; 0,25; 0,333333333333333; #DIV/0!; 0,333333333333333; 0,5; 0,333333333333333; 0,5; 0,5; 0,5; 0,25; 0,25; 0,25; 0,25; 0,333333333333333}

Step 2 - Filter Jennifers products

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

IF(logical_test, [value_if_true], [value_if_false])

IF("Jennifer"=$B$5:$B$26, 1/(COUNTIFS($B$5:$B$26, "Jennifer", $C$5:$C$26, $C$5:$C$26)), 0)

becomes

IF({TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}, {0,25; 0,5; 0,333333333333333; 0,25; #DIV/0!; 0,333333333333333; 0,25; 0,333333333333333; 0,25; 0,333333333333333; #DIV/0!; 0,333333333333333; 0,5; 0,333333333333333; 0,5; 0,5; 0,5; 0,25; 0,25; 0,25; 0,25; 0,333333333333333}, 0)

and returns

{0,25; 0,5; 0,333333333333333; 0; 0; 0; 0,25; 0,333333333333333; 0; 0; 0; 0,333333333333333; 0,5; 0; 0; 0,5; 0,5; 0; 0,25; 0; 0,25; 0}

Step 3 - Sum array

The SUM function allows you to add numbers, the function returns the sum in the cell it is entered in. The SUM function is cleverly designed to ignore text and boolean values, adding only numbers.

SUM(number1, [number2], ...)

SUM(IF("Jennifer"=$B$5:$B$26, 1/(COUNTIFS($B$5:$B$26, "Jennifer", $C$5:$C$26, $C$5:$C$26)), 0))

becomes

=SUM({0,25; 0,5; 0,333333333333333; 0; 0; 0; 0,25; 0,333333333333333; 0; 0; 0; 0,333333333333333; 0,5; 0; 0; 0,5; 0,5; 0; 0,25; 0; 0,25; 0})

and returns 4 in cell E2.

Back to top

2. Count unique distinct values based on a condition - Excel 365

This example demonstrates a smaller formula that works only in Excel 365, two out of three functions are Excel 365 functions.

Dynamic array formula in cell F28:

This formula is entered as a regular formula.

Back to top

2.1 Explaining formula in cell F28

Step 1 - Check which values meet the condition specified in cell C26

The equal sign lets you compare value to value, in this case, value to values. The output is an array of boolean values True or False.

B3:B24=C26

becomes

{"Jennifer"; "Jennifer"; "Jennifer"; "John"; "Laura"; "Laura"; "Jennifer"; "Jennifer"; "Laura"; "John"; "John"; "Jennifer"; "Jennifer"; "John"; "John"; "Jennifer"; "Jennifer"; "John"; "Jennifer"; "John"; "Jennifer"; "John"}="Jennifer"

and returns

{TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE}

Step 2 - Filter values

The FILTER function lets you extract values/rows based on a condition or criteria. It is in the Lookup and reference category and is only available to Excel 365 subscribers.

FILTER(C3:C24,B3:B24=C26)

becomes

FILTER({"Product CC"; "Product AA"; "Product BB"; "Product CC"; "Product EE"; "Product BB"; "Product CC"; "Product BB"; "Product CC"; "Product BB"; "Product EE"; "Product BB"; "Product AA"; "Product BB"; "Product DD"; "Product DD"; "Product DD"; "Product CC"; "Product CC"; "Product CC"; "Product CC"; "Product BB"},{TRUE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; FALSE})

and returns

{"Product CC"; "Product AA"; "Product BB"; "Product CC"; "Product BB"; "Product BB"; "Product AA"; "Product DD"; "Product DD"; "Product CC"; "Product CC"}.

Step 3 - Extract unique distinct values

The UNIQUE function lets you extract both unique and unique distinct values and also comparing columns to columns or rows to rows.

UNIQUE(FILTER(C3:C24,B3:B24=C26))

becomes

UNIQUE({"Product CC"; "Product AA"; "Product BB"; "Product CC"; "Product BB"; "Product BB"; "Product AA"; "Product DD"; "Product DD"; "Product CC"; "Product CC"})

and returns {"Product CC"; "Product AA"; "Product BB"; "Product DD"}.

Step 4 - Return number of rows in array

The ROWS function returns the number of rows based on a cell range or array.

ROWS(UNIQUE(FILTER(C3:C24,B3:B24=C26)))

becomes

ROWS({"Product CC"; "Product AA"; "Product BB"; "Product DD"})

and returns 4.

Back to top

Get the Excel file

Count-unique-distinct-values-based-on-a-conditionv2.xlsx