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.
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.
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 see the sample data, and test the filter, you can download the Advanced Filter for List Items sample workbook. The file is in Excel 2007 format, and is zipped.
Watch the Advanced Filter for List Items Video
To see the steps for setting up the criteria range, and running the filter, you can watch this short Excel Video tutorial.
Or watch on YouTube: Excel Advanced Filter Items in a List
___________________
没有评论:
发表评论