< 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.

=SUMPRODUCT(COUNTIF(A7:A12,{"*Best*";"*Nice*";"*Come*";"*Near*"}))>0

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.

EXAMPLE

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

=SUMPRODUCT(COUNTIF(A7:A12,{"*Best*";"*Nice*";"*Come*";"*Near*"}))>0

  • 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

avatar