In a previous article about Excel’s advanced filter,
I used a reference to Charley Kyd’s use of SUMPRODUCT to filter a list based on a custom list.
In his article, he created a formula used in a helper column to filter based on a custom list of criterias.
I realized that the same type of formula could be used in Excel’s advanced filter. A lot of the utility of using the Excel advanced filter feature is hindered by the fact that an array formula cannot be used as a criteria. However, since the SUMPRODUCT function creates a non-array formula, I surmized that it could be used as a criteria.
BTW, Rob Collie and I published an article on this same subject in PowerPivot a few years ago.
In this scenario, a list containing a column of employee numbers needs to be filtered by a custom subset of those numbers. This could be done by manually selecting those numbers from the filter dropdown criteria, which would take a very long time for a large custom list. The custom list resides on a different worksheet than the list to be filtered (in this case, on the List worksheet defined as EmployeeList).
The criteria formula (in B2) is:
Note that this formula “starts” at A5, the first item in the column.
Before applying the filter, the column looks like this:
The advanced filter information is set up as shown below.
Finally, the list after filtering looks like this:
In this example, there is only one column in the list/table. Obviously an employee table would contain many more columns. Also, the employee custom list could potentially could contain 1000’s of employee numbers. I hope that you use this technique useful in your work on employee records.
You can download the example file here.