ExcelUser logo Free guides and templates
Work In Progress...After nearly ten years, I'm redesigning ExcelUser.com. This is the new design. You can learn more here. Also, if you find something wrong with the site, please tell me about the problems. And thanks for your patience.--Charley Kyd

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.


Charley Kyd is a Microsoft Excel MVP by Charley Kyd, MBA
Microsoft Excel MVP

Excel Table with a column of SUMPRODUCT formulas that can filter on any number of criteria.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...

Step 1: Set up the SEARCH function.

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:

The SEARCH function returns #VALUE! when it can't find an item.

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...

Step 2: Turn the errors to FALSE and the successes to TRUE.

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:

Step 3: Turn the TRUEs to 1s and FALSEs to 0s.

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:

Step 4: Return TRUE if the total of all the 1s is greater than zero.

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.

 

How to Count the Items that Occur a Certain Number of Times in a List

How to Perform Multiple Table Searches Using the SEARCH & SUMPRODUCT Functions in Excel




Dashboard Reporting With Excel


Charley's SwipeFile charts