This time we are going to use SumProduct and Search together to check for near matches in data.
Search() and Sumproduct()
Combining Search() and Sumproduct() will give us the option of matching partial strings. This opens up a whole new set of possible uses for sumproduct.
Using search() alongside sumproduct()
Lets say that we have our string, separated by commas, and we want to count how many times each of the individual items appears. Sumproduct() alone, or countif is not going to do it as they want to operate on the contents of a cell, not part of the contents. Lets expand our previous sheet with a new row 6, which is going to summarize the number of items used by each of peter, paul and mary. It could be done by counting the number of times “Yes” appears in each column, but if we were summarizing, then those cells might not even exist.
Search()
Testing the number of times that Search() worked is how we are going to tackle this one. Here is the formula for C6, to be filled across
=SUMPRODUCT(–ISNUMBER(SEARCH(C1,$B$2:$B$5)))
Lets break it down..
=ISNUMBER(SEARCH(C1,$B$2:$B$5))
This will give us a series of true/false for when C1 (Peter), is found somewhere in each of the cells B2 to B5. Just converting that to a number 1/0 (using –), then adding the 1’s (using sumproduct) will tell us how man times Peter appeared anywhere in that range. Using Sumproducts ability to add up an array of results that are internal to Excel, in conjunction with other simple functions, opens up a whole selection of opportunities that would be complex or impossible using straightforward functions such as countif, sumif etc.
Read more about Quirky functions
For help and more information join our community, follow the blog or follow me on Twitter