SUMPRODUCT POWER
How to Perform Multiple Table Searches Using the SEARCH &
SUMPRODUCT Functions in Excel
SUMPRODUCT is one of Excel's mostpowerful 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, 20052014
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:
usansamonthlyblsrateunemploymentmsamtmissoula
usansamonthlyblsrateunemploymentcountymtgallatin
county, mt
usamonthlysablsrateunemploymentstatemt
usansaweeklyemploymentstateetamtcovered
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 INDEXMATCH 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 MultiCriteria 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 MultiCriteria Search Formula Works
The key to this formula is the SUMPRODUCT function, which treats
its arguments as arrays…even though the formula isn’t
arrayentered. 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 MultiCriteria 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 searchtext 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 searchtext 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 semicolons 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.
