< Go Back

Range contains one of many substrings

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.


Step 1

  • Input your data as shown below

Step 2

  • 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

Step 3

  • Click on the ENTER button
  • Your result should be as shown in figure 3

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment