Search Contextures Sites

Excel -- Filters -- Advanced Filter -- Complex Criteria

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

This page gives details on criteria used for specialized Advanced Filters.
For an introduction to Advanced Filters, click here.

Set up the Database The samples on this page assume a data layout as shown at right. The database is in columns A:D and the criteria range starts in cell F1. Note: When you use a formula as the criterion, leave the heading cell blank, or use a heading that does not match any of the table headings. For a zipped workbook with sample data and criteria, click here.

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 filter. (Note: In the case of ties, more than five records may be returned)

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

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

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.

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

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 filter. For a zipped workbook with sample data and criteria, click here.

Related Topics 1. Introduction to Advanced Filters 1. AutoFilter Basics 2. AutoFilter Tips 3. AutoFilter Programming

Home     Excel Tips     Excel Files      Blog    Contact
Privacy Policy		The Excel Store	Debra Dalgleish