## Filter unique distinct records with a condition

*Article last updated on August 14, 2017*

If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a unique distinct list based on each condition. I remember reading that Excel has difficulty with these type of or conditions in arrays.

### Answer:

**Array formula in cell C19:**

Recommended article

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

### How to create an array formula

- Create named ranges.
- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar. See above picture.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

Recommended article

Array formulas allows you to do advanced calculations not possible with regular formulas.

### Copy array formula

- Select cell C19
- Copy cell (Ctrl + c)
- Select cell range C19:D22
- Paste (Ctrl + v)

### How to remove errors

**IFERROR(**value, value_if_error**)** returns value_if_error if expression is an error and the value of the expression itself otherwise

The array formula becomes:

### How the array formula in cell C19 works

**Step 1 - Identify unique distinct records**

COUNTIFS($C$18:C18, $C$5:$C$11, $D$18:D18, $D$5:$D$11)

becomes

COUNTIFS("Category", {"Coffee";"Coffee";"Coffee";"Coffee";"tea";"juice";"tea"}, "Item", {"Espresso";"Espresso";"Americano";"Americano";"Americano";"Florida";"English"},)

and returns {0;0;0;0;0;0;0}

How to use the COUNTIFS function

Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.

**Step 2 - Filter records with condition**

COUNTIF($C$13:$C$14, $C$5:$C$11)=0

becomes

COUNTIF({"Coffee"; "tea"}, {"Coffee"; "Coffee"; "Coffee"; "Coffee"; "tea"; "juice"; "tea"})=0

and returns {FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

**Step 3 - Match filtered records**

MATCH(0, COUNTIFS($C$18:C18, $C$5:$C$11, $D$18:D18, $D$5:$D$11)+(COUNTIF($C$13:$C$14, $C$5:$C$11)=0), 0)

becomes

MATCH(0, {0;0;0;0;0;0;0}+{FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}, 0)

becomes

MATCH(0, {0;0;0;0;0;1;0},0)

and returns 1.

Identify the position of a value in an array.

**Step 4 - Return a value or reference of the cell at the intersection of a particular row and column**

INDEX($C$5:$D$11, MATCH(0, COUNTIFS($C$18:C18, $C$5:$C$11, $D$18:D18, $D$5:$D$11)+(COUNTIF($C$13:$C$14, $C$5:$C$11)=0), 0), COLUMN(A1))

becomes

INDEX({"Coffee", "Espresso";"Coffee", "Espresso";"Coffee", "Americano";"Coffee", "Americano";"tea", "Americano";"juice", "Florida";"tea", "English"}, 1, 1)

and returns "Coffee" in cell C19.

Gets a value in a specific cell range based on a row and column number.

Tip! Did you know that a pivot table can easily extract unique distinct records too?

Discover Pivot Tables – Excel’s most powerful feature and also least known

A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.

### Download excel sample file for this tutorial.

Filter unique distinct records with a condition.xlsx

(Excel 2007/2010 Workbook *.xlsx)

### Functions in this article:

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**IFERROR(**value, value_if_error**)**

Returns value_if_error if expression is an error and the value of the expression itself otherwise

**MATCH(**lookup_value, lookup_array, [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

**COUNTIFS(**criteria_range1,criteria1, criteria_range2, criteria2...**)**

Counts the number of cells specified by a given set of conditions or criteria

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

Excel 2007 pivot table: Count unique distinct records (rows)

Table of Contents Count unique distinct records Count duplicate records Excel 2007 pivot table: Count unique distinct records (rows) The […]

Count unique distinct records with a date and column criteria

davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]

Filter unique distinct records

Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]

This example sheet has 3 columns with some random data. It is quite complicated trying to manually count unique distinct […]

Filter unique distinct records using criteria

This blog post describes how to filter unique distinct rows using an excel table. This post shows you how to do […]

Filter unique distinct records (case sensitive)

The User Defined Function demonstrated above extracts unique distinct records also considering upper and lower case letters. How to use the […]

Extract unique distinct records from two data sets

The picture above shows an array formula in cell B9:C13 that extracts unique distinct records from two tables in cell […]

Extract unique distinct records based on a criterion

In a previous article "Automatically filter unique row records from multiple columns", I presented a solution to filter out unique […]

### 3 Responses to “Filter unique distinct records with a condition”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

Oscar, this works great. Keep up the great work.

When I downloaded the workbook that was uploaded to this post-reply and clicked the formula bar for cell C19 and hit 'ENTER' (without actually changing anything) and I get an #N/A record.

I'm running excel 2010, so I can only assume that the code in cell C19 'may' not work in 2010? Could this be possible? If so, can you help me with code that would work in Excel 2010??

Carlos,

When I downloaded the workbook that was uploaded to this post-reply and clicked the formula bar for cell C19 and hit 'ENTER' (without actually changing anything) and I get an #N/A record.That is one of the disadvantages with array formulas. If you edit an array formula (even though you don´t do any changes) you must enter it as an array formula. See: How to create an array formula, above.

I'm running excel 2010, so I can only assume that the code in cell C19 'may' not work in 2010? Could this be possible? If so, can you help me with code that would work in Excel 2010??It works in excel 2010.