Summary

To sum if cells contain either one text string or another (i.e. contain "cat" or "rat") you can use the SUMPRODUCT function together with ISNUMBER + SEARCH or FIND. In the example shown, the formula in cell F5 is:

=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8)) + ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)

which returns the sum the values in C4:C8 when cells  in B4:B8 contain either "cat" or "rat".

Generic formula

=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)

Explanation 

When you sum cells with "OR" criteria, you need to be careful not to double count when there is a possibility that both criteria will return true. In the example shown, we want to sum values in Column C when cells in column B contain either "cat" or "rat". We can't use SUMIFs with two criteria, because SUMIFS is based on AND logic. And if we try to use two SUMIFS (i.e. SUMIFS + SUMIFS) we will double count because there are cells that contain both "cat" and "rat".

Instead, we use a formula like this:

=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",B4:B8))+ISNUMBER(SEARCH("rat",B4:B8)))>0),C4:C8)

This core of this formula is based the the formula explained here that locates text inside of a cell with ISNUMBER and SEARCH:

ISNUMBER(SEARCH("text",range) 

When given a range of cells, this snippet will return an array of TRUE/FALSE values, one value for each cell the range. In this formula, we use this snippet twice, once for "cat" and once for "rat", so we'll get two arrays. At this point, we have:

=SUMPRODUCT(--(({TRUE;FALSE;TRUE;FALSE;FALSE}+ {TRUE;FALSE;TRUE;TRUE;FALSE})>0),C4:C8)

Next, we add these arrays together because addition is used in boolean algebra for OR logic. The math operation automatically coerces the TRUE and FALSE values to 1s and 0s, so we end up with the array below:

=SUMPRODUCT(--(({2;0;2;1;0})>0),C4:C8)

Each number in this array is the result of adding the TRUE and FALSE values in the original two arrays together. In the example shown, the array looks like this:

{2;0;2;1;0}

We need to add these numbers up, but we don't want to double count. So we need to make sure any value greater than zero is just counted once. To do that, we force all values to TRUE or FALSE by checking the array with ">0". This returns TRUE / FALSE:

=SUMPRODUCT(--({TRUE;FALSE;TRUE;TRUE;FALSE}),C4:C8)

Which we then convert to 1 / 0 using a double negative (--):

=SUMPRODUCT({1;0;1;1;0},C4:C8)

and, finally:

=SUMPRODUCT({1;0;1;1;0},{20;15;30;20;10})

SUMPRODUCT multiplies corresponding elements of the two arrays together and sums the result, returning 70.

Case-sensitive option

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

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

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.