Author: Oscar Cronquist Article last updated on January 31, 2019

This blog post describes how to filter unique distinct records that meet a given condition in an Excel defined Table. This article shows you how to do it using an array formula: Filter unique distinct records with a condition

The difference with today's blog post is that you also can add more filters to your Excel Table, making the Excel Table a lot more useful and powerful.

Formula in cell E3:

=COUNTIFS(\$B\$3:B3,B3,\$C\$3:C3,C3,\$D\$3:D3,D3)

### Explaining formula in cell E3

The COUNTIFS function was introduced in Excel 2007 and it calculates the number of cells across multiple ranges that equals all given conditions.

The criteria_range1 and criteria1 is one pair, there must be as many pairs as there are columns in your data set.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]โฆ)

 Pair criteria_range criteria Text 1 \$B\$3:B3 B3 Is value in cell B3 found in cell range \$B\$3:B3? 2 \$C\$3:C3 C3 Is value in cell C3 found in cell range \$C\$3:C3? 3 \$D\$3:D3 D3 Is value in cell D3 found in cell range \$D\$3:D3?
Note, all references in the criteria_ranges grow when the cell is copied to cells below. Example, \$B\$3:B3 contains an absolute part \$B\$3 indicated by the \$ (dollar) signs and a relative part B3 that changes in cells below.

#### Step 1 - The first argument pair

\$B\$3:B3 returns 40475 and B3 returns 40475.

The value in cell B3 is found once in cell range \$B\$3:B3.

#### Step 2 - The second argument pair

\$C\$3:C3 returns "AA" and C3 returns "AA".

The value in cell C3 is found once in cell range \$C\$3:C3.

#### Step 3 - The third argument pair

\$D\$3:D3 returns 1 and D3 returns 1.

The value in cell D3 is found once in cell range \$D\$3:D3.

#### Step 4 - All arguments

The COUNTIFS function returns 1 because all argument pairs return 1 meaning this record in row 3 is a unique distinct record, however, there might be duplicate records further down the list.

COUNTIFS(\$B\$3:B3,B3,\$C\$3:C3,C3,\$D\$3:D3,D3)

becomes

COUNTIFS(40475,40475,"AA","AA",1,1)

and returns 1.

The image above shows that row 16, 17 and 19 contain duplicate records.

#### What are unique distinct rows/records?

Unique distinct rows are all rows but duplicate records are merged into one distinct record, see example in the picture below.