This post demonstrates how to calculate unique distinct products (Column E) and meeting a criterion or multiple criteria.

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))

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.

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}

 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}

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.

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)

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)

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)

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

Recommended blog posts