Excel 2007 introduced the powerful Table feature, as illustrated below. Tables allow you to sort and filter your data easily.
However, the filter capability has at least two problems. First, you can use a maximum of only two criteria to filter any column.
Second, it takes about half a dozen steps each time you change a filter. So changing filters isn’t a quick process. It would be so much easier if you could just copy and paste one or more filters to a range of cells, or even use formulas to change filters.
This figure illustrates a way around these problems.
In the figure, the Table’s Items column is just a list of text items in column A of the spreadsheet. Column D, which isn’t part of the Table, shows a list of search criteria for the Items column. The formula in column B’s Found column returns TRUE if the formula finds any item in the SearchFor list in the adjacent item in column A.
After you update the SearchFor list, you just press the F9 key (if your manual calculation mode is set), and then filter the Found column for TRUE if you want only the items found, or FALSE if you want the items not found.
The SUMPRODUCT Formula
Here’s the SUMPRODUCT formula I used in column B above:
B3: =SUMPRODUCT(NOT(ISERROR(SEARCH(SearchFor,[@Items])))+0)>0
If you’ve not worked with Excel Tables before, the “[@Items]” argument will look strange to you. The argument says, “Return the value from the current row of the Items column.” That is, it works the same in cell B3 as using the cell reference A3 here:
B3: =SUMPRODUCT(NOT(ISERROR(SEARCH(SearchFor,A3)))+0)>0
In fact, when you type this formula into cell B3, but click cell A3 rather than typing it in, Excel returns “[@Items]” instead. So you really don’t need to know the Table syntax when you enter your formula.
There’s another question that needs a longer explanation, however: How does this SUMPRODUCT formula do its work? The best way to answer this question is to follow the path I used to create the formula in the first place.
To do so, let’s work with cell B4, which should return TRUE when the formula in that cell is working correctly…
The SEARCH formula in cell B4 returns a #VALUE! error because it’s trying to return multiple search results into one cell. (In other words, it’s trying to return one result for each item in the SearchFor list.) To see what those results are, click anywhere in the formula bar then press F9 to calculate the formula. When you press F9, you get this result:
Here, Excel searched for “shoes” in “Sports Coats” and failed; it searched for “b?w” and failed; it searched for “c*s” and succeeded, returning “8”, then it searched for the three sets of dashes, and failed. So it returned the array of error values shown, with the 8 sandwiched in the middle.
After you calculate a result in the formula bar, as I did here, press Esc to return the original formula.
Our next step is to turn those error values into FALSE values, and the “8” to TRUE values. To do so, we surround the SEARCH function with NOT(ISERROR(…)), giving us…
Because we want to sum the results, we need to turn those TRUEs and FALSEs into 1s and 0s (ones and zeros). The easiest way to do this is to add zero to the formula. (This works because TRUE + 0 =1 and FALSE + 0 = 0.) This gives us:
B4: =NOT(ISERROR(SEARCH(SearchFor,[@Items])))+0
Testing the change in the formula bar, we now see:
We now need to add up those results. If the total is greater than zero, we know that at least one of the filters worked. We add the results by expanding the formula again…
B4: =SUMPRODUCT(NOT(ISERROR(SEARCH(SearchFor,[@Items])))+0)>0
This is the final version of the formula. We now can copy it to the remainder of the Table, which gives us this result:
We use SUMPRODUCT here for two reasons. First, this function sums the values in an array even when there’s no multiplication—no “product”—in the formula. So it does what we want. Second, it saves our needing to array-enter a different version of the formula.
If you don’t mind arrays in your Table, you can array-enter this shorter formula to give you the same results:
B4: =OR(NOT(ISERROR(SEARCH(SearchFor,[@Items]))))
Using the OR function works because OR returns TRUE if any of its arguments is TRUE, which is what we want. But unfortunately, we must tell OR to treat its arguments as an array, which is why we must array-enter the formula.
A Brief Note About the SearchFor List
If the SearchFor list contains any empty cells, the entire Found column returns TRUE. Therefore, we need to define this list carefully.
One option is to use a dynamic range name to define only the list of search terms we’re using. But I won’t discuss that approach here.
Another option is to enter a few search items that you know your list doesn’t contain, like the three sets of dashes shown in column D above. Then, when you want to add an additional search term to your list, just replace a set of dashes with the search text you actually want to use.
Don’t include too many sets of dashes, however, because each additional item in the list does take extra time to calculate. So if the list in column A is very long, you could slow calculation noticeably.
