If you want to determine whether a RANGE of items in some cells contain certain values, the SUMPRODUCT and COUNTIF functions prove useful.
How to Check if a Range of Cells Contains One of Many Substrings
In the example below, Cell F7 is worked out with the basic formula below.
The COUNTIF function helps to count the contents of the substrings that appear within the chosen RANGE (A7:A12). Because the COUNTIF function is laden with multiple values to search for, every count for a value is received in the form of 0 and 1.
The SUMPRODUCT function returns as TRUE any value that is greater than ZERO.
The combination of these excel functions is great for checking a bulky document for particular substrings that are desired.
- Input your data as shown below
- Click on Cell F7
- Type this into the fx bar. You can also copy and paste it
- What you have should be similar to figure 2 below
- Click on the ENTER button
- Your result should be as shown in figure 3