Search Contextures Sites

 

Contextures
Excel news
by email

 

 

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

 

30 Excel Functions in 30 Days

 

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

Advanced Filter -- Complex Criteria

Set up the Database
Extract the Top Five Records
Extract Top Records After Specific Date
Filter for Matching Data in Two Columns
Filter for Unmatched Data in Two Columns
Video: Filter for Unmatched Data
Find Numbers within a Number
Filter Rows with Blank Cells
Filter Items in a List

This page gives details on criteria used for specialized Excel Advanced Filter.
For basic information, please visit the introduction to Excel Advanced Filter page.

Set up the Database

The samples on this page assume a data layout as shown below. The database is in columns A:D and the Excel advanced filter criteria range starts in cell F1.

advanced filter setup

Note: When you use a formula as the Excel advanced filter criterion, leave the heading cell blank, or use a heading that does not match any of the table headings.

Extract the Top Five Records

F1: Criteria Heading -- Leave blank
F2: Criteria:   =D2>=LARGE($D$2:$D$28,5)

The LARGE function returns the fifth largest value in the range D2:D28.
An absolute reference is used for the range -- $D$2:$D$28.

Cell D2, the first cell with data in the Total column, is compared to this value. A relative reference is used for this -- D2. Rows with a Total value that is greater than or equal to the fifth largest number, pass through the Excel advanced filter. (Note: In the case of ties, more than five records may be returned)

advanced filter top 5

Extract Top Records After Specific Date

In this example, the filter will extract the five records with highest totals, for records that were entered on or after a specific date. If you download the sample file, there is also an example for finding the highest totals within a specific date range.

NOTE: To see the steps for showing top 10 based on additional criteria in an AutoFilter, go to my blog post on Top 10 in Filtered Rows

H2: Type the starting date for the filtered records (Feb 3 in this example)
I2: Type the number of top records to return (5 in this example)
K2: Formula calculates the number of records after the start date: =COUNTIF($A$3:$A$29,">=" & $H$2)
M2: Formula calculates 5th largest Total for orders on or after Feb 3rd

=LARGE(IF($A$3:$A$29>=$H$2,$D$3:$D$29),MIN($K$2,$I$2))

  • The LARGE function returns the fifth largest value in the range D3:D29, for dates on or after Feb 3rd.
    An absolute reference is used for each range, such as $A$3:$A$29.
  • NOTE: This formula is arrayed enterd (Ctrl+Shift+Enter), to create a LARGE IF formula -- it returns the 5th largest total for the records IF the date is greater than Feb 3rd.
  • NOTE: If there are fewer records than the Top # entered, the lower number is used -- MIN($K$2,$I$2)

    advanced filter top date start

F1: Criteria Heading -- Leave blank
F2: Criteria:   =AND(A3>=$H$2,D3>=$M$2)

In cell F2, the criteria formula checks two things:

  • Cell A3, the first cell with data in the Date column, is compared to this value. A relative reference is used for this -- A3. Rows with a Total value that is greater than or equal to the fifth largest number, and a date on or after Feb 3rd, pass through the Excel advanced filter. (Note: In the case of ties, more than five records may be returned)
  • The AND function adds a second criiteria to the filter -- the total must be greater than the minimum calculated in cell M2
    AND(.....D3>=$M$2)

advanced filter criteria AND

In the screen shot below, the top five records with dates on or after Feb 3rd are in the filtered results.

advanced filter top date results

Filter for Matching Data in Two Columns

F1: Criteria Heading -- Leave blank
F2: Criteria:   =C2=D2

In each record, the Order date is compared to the Ship date. A relative reference is used for this -- C2 and D2. Rows where the dates are equal pass through the Excel advanced filter.

advanced filter match

Filter for Unmatched Data in Two Columns

F1: Criteria Heading -- Leave blank
F2: Criteria:   =C2<>D2

In each record, the Invoice amount is compared to the amount Paid. A relative reference is used for this -- C2 and D2. Rows where the amounts are not equal are displayed.

advanced filter unmatched

Note: You can use a column heading in the criteria formula, instead of a cell reference. The formula will return the #NAME? or #VALUE! error, but the filter will work correctly. For example:

F2: Criteria:   =Invoice<>Paid

Video: Filter for Unmatched Data

To see the steps for creating an Advanced Filter for unmatched amounts in a row, please watch this short video. The written instructions are shown above.

Find Numbers within a Number

F1: Criteria Heading -- Leave blank
F2: Criteria:   =ISNUMBER(FIND("8",C2))

The FIND function looks for the string "8" in the contents of cell C2.
If it finds the number, the result is the location of the number in the string. If the number is not found, an Error is the result.

advanced filter find number

Filter Rows with Blank Cells

F1: Criteria Heading -- Leave blank
F2: Criteria:   =C2=""

In each record, the value in column C is checked. If it is an empty string, the record passes through the Excel advanced filter.

advanced filter find blank

Filter Items in a List

Column H: Enter the list of items that you want to match
F1: Criteria Heading -- Leave blank
F2: Criteria:   =COUNTIF(H:H,C2)

In each record, the value in column C is checked. If it is in the list in column H, the record passes through the Excel advanced filter.

advanced filter find in list

Download the Sample File

For a zipped workbook with sample data and Excel advanced filter criteria, click here.

Learn how to create Excel dashboards.

More Filter Tutorials

1. Excel Advanced Filter Introduction

1. AutoFilter Basics
2. AutoFilter Tips
3. AutoFilter Programming

   

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.

 

Last updated: July 26, 2014 4:06 PM