Exceljet

Quick, clean, and to the point

Sum if cells contain either x or y

Excel formula: Sum if cells contain either x or y
Generic formula 
=SUMPRODUCT(--((ISNUMBER(SEARCH("cat",rng1)) + ISNUMBER(SEARCH("rat",rng1)))>0),rng2)
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".

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.