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.