This post demonstrates how to build an array formula that counts unique distinct values using a criterion or criteria.

Tip! I highly recommend you use a pivot table if you own Excel 2013 or a later version, it is easier to understand and much faster if you have lots of data:
Count unique distinct values [Pivot Table]

Table of Contents

  1. How many unique distinct products did Salesperson Jennifer sell?
  2. How many unique distinct products did Jennifer sell in January?
  3. How many unique distinct products did Jennifer sell in January and in region South?
  4. How many unique distinct products was sold in the south or in January?

Example 1,

How many unique distinct products did Salesperson Jennifer sell?

Array formula in cell E2:

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

Recommended article:

Count unique distinct values in a column in excel

Question: I have a list of values (A1:A6), how do I count unique distinct values? Answer: Table of Contents Count […]

Comments(26) Filed in category: Count values, Excel, Unique distinct values, Unique values

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.

Recommended article:

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Comments(2) Filed in category: Built-in features, Count values, Excel

Explaining array formula in cell E2

Step 1 - Calculate unique distinct products that Jennifer sold

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

1/COUNTIFS($D$5:$D$26, "Jennifer", $E$5:$E$26, $E$5:$E$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}

Recommended article:

COUNTIFS function

Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.

Comments(1) Filed in category: Excel, Functions

Step 2 - Filter Jennifers products

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

IF("Jennifer"=$D$5:$D$26, 1/(COUNTIFS($D$5:$D$26, "Jennifer", $E$5:$E$26, $E$5:$E$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}

Recommended article:

IF function explained

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Comments(9) Filed in category: Excel, Functions

Step 3 - Sum array

=SUM(IF("Jennifer"=$D$5:$D$26, 1/(COUNTIFS($D$5:$D$26, "Jennifer", $E$5:$E$26, $E$5:$E$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.

Recommended article:

Excel SUM function

The SUM function in excel allows you to add values, the function returns the sum in the cell it is […]

Comments(0) Filed in category: Excel, Functions, Sum

Example 2,

How many unique distinct products did Jennifer sell in January?

Array formula in C3:

=SUM(IF(("Jennifer"=$D$6:$D$27)*($B$6:$B$27<=DATE(2011, 1, 31)), 1/COUNTIFS($D$6:$D$27, "Jennifer", $E$6:$E$27, $E$6:$E$27, $B$6:$B$27, "<="&DATE(2011, 1, 31))), 0)

This calculation is also possible in a pivot table, you simply add more criteria: Count unique distinct values [Pivot Table]

Recommended article:

Count unique distinct records in excel 2007

This example sheet has 4 columns with some random data. It is quite complicated trying to count unique distinct records […]

Comments(2) Filed in category: Count values, Excel, Unique distinct records

Example 3,

How many unique distinct products did Jennifer sell in January and in region South?

Array formula in D3:

=SUM(IF(("Jennifer"=$D$6:$D$27)*($B$6:$B$27<=DATE(2011, 1, 31))*("South"=$C$6:$C$27), 1/COUNTIFS($D$6:$D$27, "Jennifer", $E$6:$E$27, $E$6:$E$27, $B$6:$B$27, "<="&DATE(2011, 1, 31), $C$6:$C$27, "South")), 0)

Recommended article:

Count unique distinct values with a condition

Stephen asks: Could you help me with (1) the Count the number of unique Divisions where ALL products have been […]

Comments(3) Filed in category: Conditional formatting, Count values, Excel, Unique distinct values

Example 4

How many unique distinct products was sold in the south or in January?

Array formula:

=SUM(--(FREQUENCY(IF((($B$10:$B$31<=DATE(2011, 1, 31))*($B$10:$B$31>=DATE(2011, 1, 1)))+("South"=$C$10:$C$31), COUNTIF($E$10:$E$31, "<"&$E$10:$E$31), ""), COUNTIF($E$10:$E$31, "<"&$E$10:$E$31))>0))

Download excel sample file for this tutorial.

Count unique distinct values meeting criteria.xlsx
(Excel 2007 Workbook *.xlsx)

Recommended article:

Count records between two dates in excel

Someone googled "Count records between date range" and landed on my website. I realize I have not covered this problem. […]

Comments(22) Filed in category: Count values, Dates, Excel

Count unique distinct values in a filtered table

A few days ago Debra Dalgleish described how to create a Line Between Dates in Filtered List. She modified a […]

Comments(7) Filed in category: Count values, Excel, Unique distinct values

Functions in this post:

SUM(number1,[number2],)
Adds all the numbers in a range of cells

IF(logical_test.[value_if:true],[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...)
Counts the number of cells specified by a given set of conditions or criteria

FREQUENCY(data_arraybins_array)
Calculates how often values occur within a range of values and then returns a vertical array of numbers having one more element than the bins_array

DATE(year,month,day) returns the number that represents the datein Microsoft Office Excel date-time code