SUMPRODUCT Power
Use SUMPRODUCT in an Excel Table to Filter Any Number of Items
In Excel Tables, you can filter on any two conditions in a column. But wouldn't it be
nice to filter on any number of items in a separate list? You can, if you
use Excel's SUMPRODUCT function in a calculated column.
Excel
2007 introduced the powerful Table feature, as illustrated here. 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 arrayenter a
different version of the formula.
If you don't mind arrays in your
Table, you can arrayenter 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
arrayenter 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.
