Explanation
In this example, the goal is to sum the numbers in column C when the text in column B contains specific pairs of colors. For example, the formula should sum a number when the text contains both “red” and “blue”. Order is not important; the two colors can appear anywhere in the cell. However, both colors must appear in the same cell. This problem can be solved with the SUMIFS function , which is designed to sum numbers based on multiple criteria.
SUMIFS function
The SUMIFS function sums cells in a range that meet one or more conditions, referred to as criteria . The syntax for the SUMIFS function depends on the number of conditions needed. Each separate condition will require a range and criteria . The generic syntax for SUMIFS looks like this:
=SUMIFS(sum_range,range1,criteria1) // 1 condition
=SUMIFS(sum_range,range1,criteria1,range2,criteria2) // 2 conditions
In this case, we need two conditions, one to test for “red”, and one to test for “blue”. This means both criteria will be applied to the same range, the text in B5:B16. We start with the sum_range , which is the numbers in the range C5:C16:
=SUMIFS(C5:C16,
Then we add the first range/criteria pair to test for “red”:
=SUMIFS(C5:C16,B5:B16,"*red*",
Note that we surround “red” with an asterisk () on either side. The asterisk () is a wildcard available in the SUMIFS function, which means “zero or more characters”. We use a wildcard in this case to match “red” occurring anywhere in the text. Next, we add a second range/criteria pair to test for “blue” to complete the formula:
=SUMIFS(C5:C16,B5:B16,"*red*",B5:B16,"*blue*")
Again, we use two asterisks (*) as wildcards to match “blue” in any location. Notice we are applying two different criteria to the same range , B5:B15. This is intentional. The SUMIFS function applies criteria based on AND logic, which means that both conditions must be true for SUMIFS to include a value in the final result. In other words, both “red” and “blue” must exist in the text. Note that SUMIFS is not case-sensitive. Using “red” for criteria will match “Red”, “RED”, and “red” in any location.
Other combinations
The other color combinations in the worksheet shown use the same pattern. To test for “pink” and “purple”, and “green” and “blue”, the formulas in F6 and F7 are as follows:
=SUMIFS(C5:C16,B5:B16,"*pink*",B5:B16,"*purple*")
=SUMIFS(C5:C16,B5:B16,"*green*",B5:B16,"*blue*")
Explanation
In this example, the goal is to sum numbers in the range C5:C16 when text in the range B5:B16 contains the substring “red” OR the substring “blue”. In other words, if the text in B5:B16 contains either of these two text values in any location, the corresponding number in C5:C16 should be included in the sum. We can’t use the SUMIFS function with two criteria because SUMIFS is based on AND logic, and both criteria would need to be TRUE. And if we try to use two SUMIFS formulas (i.e., SUMIFS + SUMIFS), we will double count some numbers because some cells in B5:B16 contain both “red” and “blue”. One solution is to use the SUMPRODUCT function together with the ISNUMBER and SEARCH functions.
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 the position of the substring in the text as a number. If the substring is not found, SEARCH returns a #VALUE error. For example:
=SEARCH("p","apple") // returns 2
=SEARCH("z","apple") // returns #VALUE!
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:
=ISNUMBER(SEARCH("p","apple")) // returns TRUE
=ISNUMBER(SEARCH("z","apple")) // returns FALSE
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:
=SUMPRODUCT(--((ISNUMBER(SEARCH("red",B5:B16)) + ISNUMBER(SEARCH("blue",B5:B16)))>0),C5:C16)
Working from the inside out, the array1 inside SUMPRODUCT is composed of this snippet:
--((ISNUMBER(SEARCH("red",B5:B16)) + ISNUMBER(SEARCH("blue",B5:B16)))>0)
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:
{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE}
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:
{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE}
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:
=SUMPRODUCT(--(({1;0;1;0;0;0;0;1;1;0;1;0} +{1;0;1;0;0;1;1;0;0;0;1;1})>0),C5:C16)
After adding the two Boolean arrays together, we have:
=SUMPRODUCT(--({2;0;2;0;0;1;1;1;1;0;2;1}>0),C5:C16)
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:
=SUMPRODUCT({1;0;1;0;0;1;1;1;1;0;1;1},C5:C16)
Next, SUMPRODUCT multiplies corresponding elements of the two arrays together and sums the result:
=SUMPRODUCT({1;0;1;0;0;1;1;1;1;0;1;1},C5:C16)
=SUMPRODUCT({1;0;1;0;0;1;1;1;1;0;1;1},{2;5;3;3;4;5;1;4;2;5;1;5})
=SUMPRODUCT({2;0;3;0;0;5;1;4;2;0;1;5})
=23
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”.
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 .
