This post demonstrates how to build an array formula that counts unique distinct values based on 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]

Discover Pivot Tables – Excel’s most powerful feature and also least known

A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.

Table of Contents

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

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)

Watch this video where I explain how the above formula works:

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

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

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 based on a condition

The following article demonstartes how to construct a formula that counts unique distinct values based on a condition. The image […]

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

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

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 […]

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