We can check if a range of cells contains one of many substrings with the **SUMPRODUCT **and **COUNTIF **functions. This article will walk through the process.

*Figure 1: How to Check if a Range of Cells Contains One of Many Substrings*

**Formula**

**=SUMPRODUCT(COUNTIF(A4:A9,{"*Rome*";"*Giant*";"*Five*"}))>0**

**Setting up the Data**

- We will input the data into the table as shown in figure 2
- We will check the
**range (A4:A9)**if they contain any of the**substrings in B4:B6**

* Figure 2: Data to Check if a Range of Cells Contains One of Many Substrings*

**Check if a Range of Cells Contains One of Many Substrings **

- We will click on
**Cell C4** - We will input the formula below into the cell

**=SUMPRODUCT(COUNTIF(A4:A9,{"*Rome*";"*Giant*";"*Five*"}))>0**

- We will
**press the enter key**to get the result

*Figure 3: Result for the Range of Cells Showing that it Contains Rome and Giant*

**Explanation**

The **COUNTIF **function counts the contents of the substrings that appear within the chosen **RANGE (A4:A9).** 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.**

**Instant Connection to an Expert through our Excelchat Service**

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

## Leave a Comment