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:
. . . are given the name 'Bums'.
2. Now in a helper column to the right of the data we enter this formula:
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.