For business users of Microsoft Excel Free guides and templates

SUMPRODUCT POWER

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

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.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

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

My previous post 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, I highly recommend the use of 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 if you add a Tags column to your Table, you’ll finally be able to have information that makes sense to YOU.

I’ll expand on this idea in a future article, but here’s how to get started:

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

Take Your Next Steps

If you're looking for additional help with this topic, I can help you in three ways. To learn more, see Excel Training, Coaching, and Consulting.

 

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

Use SUMPRODUCT in an Excel Table to Filter Any Number of Items

How to Use SUMPRODUCT to Create Two-Dimensional Lookups in Excel Formulas

Free Excel Dashboards


Charley's SwipeFile charts