The result is 23, the sum of numbers in C5:C16 when text in B5:B16 contains the substring “red” or the substring “blue”.

SEARCH + ISNUMBER for substrings

The core of this formula is based on the SEARCH function together with the ISNUMBER function. The SEARCH function is designed to find a specific substring in a text string. If SEARCH finds the substring, it returns a position of the substring in the text as a number. If the substring is not found, SEARCH returns a #VALUE error. For example: To force a TRUE or FALSE result, we can use the ISNUMBER function. ISNUMBER returns TRUE for numeric values and FALSE for anything else. So, if SEARCH finds the substring, it returns the position as a number, and ISNUMBER returns TRUE: If SEARCH doesn’t find the substring, it returns an error, which causes the ISNUMBER to return FALSE. For a more detailed explanation of this approach, see this page.

SUMPRODUCT function

In the example shown, the formula in cell F5 is: Working from the inside out, the array1 inside SUMPRODUCT is composed of this snippet: On the left, SEARCH is configured to look for “red”. Because there are 12 values in the range B5:B16, ISNUMBER returns an array with 12 results: Each TRUE in the array represents a cell in B5:B16 that contains “red”. On the right, SEARCH is configured to look for “blue”. This results in the array below: Each TRUE in this array represents a cell in B5:B16 that contains “blue”. Next, we add these arrays together. We use addition (+) because addition corresponds to OR logic in Boolean algebra. The math operation automatically coerces the TRUE and FALSE values to 1s and 0s, so we can now rewrite the original formula like this: After adding the the two Boolean arrays together, we have: The 2s in this array represent cells that contain both “red” and “blue”. To avoid double counting, we force the numbers back to TRUE and FALSE by comparing to zero, then use a double negative (–) to convert the TRUE and FALSE values to 1s and 0s. The final value of array1 in SUMPRODUCT is now: Next, SUMPRODUCT multiplies corresponding elements of the two arrays together and sums the result: The final result is 23, the sum of numbers in C5:C16 that correspond to text in B5:B16 that contains either “red” or “blue”. Note: In Excel 365, you can replace SUMPRODUCT with the SUM function. To read more about this, see Why SUMPRODUCT?

Case-sensitive option

The SEARCH function ignores case. If you need a sensitive option, you can replace the SEARCH function in this formula with the FIND function.

Dave Bruns

Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.