Author: Oscar Cronquist Article last updated on May 19, 2018

This post demonstrates how to build an array formula that counts unique distinct values based on criteria.

Unique distinct values are all values except that duplicates are merged into one value, in other words, duplicates are removed.

I highly recommend you use a pivot table if you own Excel 2013 or a later version.

A pivot table is easier to work with and much faster if you have lots of data:
Count unique distinct values [Pivot Table]

An array formula is great for an interactive dashboard or dynamic data meaning data changes often, like once a week or perhaps once a month.

If you use an Excel defined Table or a dynamic named range you can quickly change the data range without editing the cell references in the array formula.

How to count unique distinct items based on a condition and a date condition?

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

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 demonstrates 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 dates inside a date range

How do I automatically count dates in a specific date range? Array formula in cell D3: =SUM(IF(($A$2:$A$10<$D$2)*($A$2:$A$10>$D$1), 1, 0)) + […]

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:

Adds all the numbers in a range of cells

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

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