top of page

SUMPRODUCT formula to find if multiple strings are contained in column of data


You can use a modified SUMPRODUCT formula to find which cells in a column contain any one of multiple strings. Follow these steps:

1. In this example we have listed the strings we want to search for in column R. We select the strings and then in the name box on the upper left designated a name for the group of strings. The names:

Hodges

Robinson

Reese

Campanella

. . . are given the name 'Bums'.

2. Now in a helper column to the right of the data we enter this formula:

=SUMPRODUCT(--ISNUMBER(SEARCH(Bums,A2)))>0

We search for each string listed in 'Bums' in the first column, A.

3. When any part of the entry in column A for a particular row lists the string, the value TRUE is returned.


Sean O'Shea has more than 20 years of experience in the litigation support field with major law firms in New York and San Francisco.   He is an ACEDS Certified eDiscovery Specialist and a Relativity Certified Administrator.

The views expressed in this blog are those of the owner and do not reflect the views or opinions of the owner’s employer.

If you have a question or comment about this blog, please make a submission using the form to the right. 

Your details were sent successfully!

© 2015 by Sean O'Shea . Proudly created with Wix.com

bottom of page