SUMPRODUCT is one of Excel’s most-powerful worksheet functions. Here, for example, you can use it in one formula to search text in one cell for many items.
In How to Add Advanced Filter Capabilities to Excel Tables, I explained how to use a long formula within a Table to simplify complex filtering.
The formula relies on Excel’s SEARCH worksheet function, which gives us the power to search for one string within another string. The search is not case sensitive and it can use wildcards.
Unfortunately, however, SEARCH was designed to search for only one string at a time. This limitation has been a problem for me because I often need to include more than two criteria when I’m filtering data in one column.
To see what I mean, take a look at the content of four cells from aTags column in my Excel Table:
|usa|nsa|monthly|bls|rate|unemployment|msa|mt|missoula|
|usa|nsa|monthly|bls|rate|unemployment|county|mt|gallatin county, mt|
|usa|monthly|sa|bls|rate|unemployment|state|mt|
|usa|nsa|weekly|employment|state|eta|mt|covered|
What if I want to look at unemployment data in Montana while ignoring counties, Metropolitan Statistical Areas (“msa”), and seasonally adjusted (“sa”) data? To do so, I’d need to apply five filters.
How to Add Advanced Filter Capabilities to Excel Tables explained that an effective way to do this is to set up one filter column within my Table, a column with formulas that return TRUE when all conditions are met; otherwise, they return FALSE. In that post, however, the formula required the use of multiple SEARCH functions per cell searched.
But now, I’m going to introduce a formula that needs to use only one SEARCH function per cell searched…no matter how many filters you want to apply to each cell.
An Interruption: Why You Should Add Tags to Your Excel Tables
The tags I listed above describe economic data available from the Federal Reserve Bank of St. Louis. But even if you care NOTHING about economic data, you might occasionally need a Tags column for working with your data in Excel Tables. Here’s why:
Most of your data probably is generated by the IT department, or by commercial programs. Therefore, you probably have no control over the codes and descriptions — the meta data — that your Excel Tables contain. But when you add a Tags column to your Table, you’ll be able to have information that makes sense to YOU.
To illustrate, your Table probably includes a column with a code that uniquely identifies each row, a Series ID, a GL Account Number, a SKU, a product number, whatever.
Therefore, you could maintain a separate Table with a column of codes and your own Tags column. Then, when you open a new version of the data as an Excel Table, you can add a column with formulas that use VLOOKUP or INDEX-MATCH to add your custom Tags column to the standard data.
It might take some effort to tag each row of data. But you’ll only need to tag each row once (unless you change the tag, which you’re free to do). From that point on, you’ll be able to use your custom tags to look at Table data from YOUR perspective.
Introducing the Multi-Criteria Search Formula
This formula uses one SEARCH function to search the text in any cell for any number of items in a list. It returns a summary of its findings as a single value. Then a test of that value causes the formula to return TRUE or FALSE to indicate whether the cell matches all criteria. Here’s the formula in four rows:
=SUMPRODUCT(NOT(ISERR(
SEARCH({“mt”,”msa”,”county”,”unemployment”,”|nsa|”},[@Tags])
))*{1,2,4,8,16})
=9
For each test that the SEARCH function performs and passes, the SUMPRODUCT function adds the comparable number to its total. So if a search finds only “mt” and “unemployment” in the text, SUMPRODUCT adds 1 plus 8. If that is the condition you want, the formula will return TRUE when you test for the value 9, as shown here.
On the other hand, if you also want “county” data, you would include its value, 4, in your total. That is, you would test for 13 rather than 9.
How the Multi-Criteria Search Formula Works
The key to this formula is the SUMPRODUCT function, which treats its arguments as arrays…even though the formula isn’t array-entered. The function sets up a temporary column within memory, which performs the SEARCH test for each item in the list.
We don’t care where the search text is found in the list, we merely want to know if it’s there. So we surround the SEARCH function with the NOT(ISERR(…)) functions. If the item IS found, there’s no error. So ISERR returns FALSE and the NOT function switches the result to TRUE. So TRUE indicates that the search text IS found.
On the other hand, if the search text isn’t found, SEARCH returns an error value. So ISERR returns TRUE, which the NOT function switches to FALSE. So FALSE indicates the the search text is NOT found.
Finally, the SUMPRODUCT function multiplies these TRUE or FALSE results by the corresponding numbers in the list. Since TRUE equals 1 and FALSE equals zero, SUMPRODUCT adds up the the numbers for the items found. The numbers are chosen so that every sum represents a unique combination of values. Therefore, we can test for one number to specify any combination of search successes and failures we want.
Extending the Multi-Criteria Search Formula
Here’s the formula again:
=SUMPRODUCT(NOT(ISERR(
SEARCH({“mt”,”msa”,”county”,”unemployment”,”|nsa|”},[@Tags])
))*{1,2,4,8,16})
=9
You can modify and extend it in a variety of ways. For example…
…If you’re interested in any Montana county information other than unemployment, you would test for the value 5. (This means that searches for “mt” (1) and “county” (4) must succeed and that all other searches fail.)
…If you’re interested in unemployment information for any city outside of Montana, you would test for the value 10. (Searches for “msa” (2) and “unemployment” (8) succeed and all other searches fail.)
…If you decide that you temporarily don’t care whether the “county” tag exists or not, you can replace the value 4 in the list with zero. Or, if you want to temporarily select for any state, you can replace the value 1 in the list with zero.
…If you want to use a row of search-text items in the range F3:J3, rather than the row of array constants in the formula, you could change the formula to:
=SUMPRODUCT(NOT(ISERR(SEARCH($F$3:$J$3,[@Tags])))*{1,2,4,8,16})
…If you want to use a column of search-text items in the range D3:D7, rather than a row of items, you could change the formula to:
=SUMPRODUCT(NOT(ISERR(SEARCH($D$3:$D$7,[@Tags])))*{1;2;4;8;16})
(Notice the semicolons between numbers in the array constant at the end of this formula. The semi-colons signify a column of data rather than a row.)
…If you want to use this search technique for a cell that’s not in a table, replace “[@Tags]” with a cell reference.
…If you want to test for more than five items, merely add them to your list and add successive powers of 2 to your list of numbers. For example, if you want to test for eight items, your number list would be {1,2,4,8,16,32,64,128}.
Finally, if you want to search two different cells for a list of items, you would use two SUMPRODUCT tests like the one above, with both of them contained in one AND function, like this:
=AND([whatever],[whatever])
Of course, if you want to search four cells, you would enclose four SUMPRODUCT tests in your AND function.
