On Monday, we declared this Excel Advanced Filter Week, in honour of the three filter feeding shark species. Who said Excel wasn’t exciting?
Today, we’ll see how to use the Advanced Filter to select only the items that are in a list, when running the filter.
Video: Filter for List Items
To see the steps for setting up the criteria range, and running the filter, you can watch this short Excel Video tutorial. There are written steps below the video.
To get the Excel file, so you follow along with the video, go to the Excel Advanced Filter Criteria page on my Contextures site.
In the download section, look for the Match Items in List sample workbook.
Advanced Filter Criteria Range
For this Advanced Filter, we want to filter for orders that total more than $1000, and have that criterion in the criteria range, shown below.
We also want to limit the products – only including the items that are listed in column I.
Filter for List Items in Advanced Filter
For a short list of items, you could create multiple rows in the criteria range, and list each item separately. For longer lists, that would be impractical.
Instead, you can use a formula, to check each row, and see if its product is in the list. We’ll add a second column to the criteria range, and put the formula there.
In this example, the first product data is in cell C2, so the formula in cell G2 is:
=COUNTIF(I:I,C2)
The COUNTIF function returns the number of instances of the product in the list in column I. If the product isn’t found, the count is zero. Only the records with a count greater than zero would pass through the filter.
Criteria Headings
Because you’re using a formula in an Advanced Filter criteria range, the heading can’t match any of the source data headings.
You can either clear the heading cell in the criteria range, or type a different heading. I left cell G1 blank, and the criteria range is F1:G2.
Run the Advanced Filter
After you set up the criteria range, you can run the Advanced Filter. Remember, if you want the results on a different worksheet, select that destination sheet before you run the filter.
In this example, the filter is started from the ListOrders sheet, and the list and criteria range are on the Orders sheet.
Download the Advanced Filter List Workbook
To get the Excel file, so you can see the sample data, and test the filter, go to the Excel Advanced Filter Criteria page on my Contextures site.
In the download section, look for the Match Items in List sample workbook. The zipped file is in Excel xlsx format, and does not contain any macros.
___________________