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

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

The blue arrows shows unique distinct products based on sales person "Jennifer", the total number match with the number in cell E4. The remaining highlighted records are only duplicate values.

Array formula in cell E2:

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

Learn to build formulas that count unique distinct values based on criteria:

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. Tip! I highly […]

Comments(93) Filed in category: Count unique distinct values, Count values, Excel

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: Count values, Excel

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, click on "Evaluate formula" button.

Click on "Evaluate" button shown in above image to move to next step.

Step 1 - Calculate unique distinct products that Jennifer sold

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}

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

Step 2 - Filter Jennifers products

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}

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

Step 3 - Sum array

=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.

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, SUM function

Download excel *.xlsx file

Count unique distinct values based on a condition.xlsx