The result is 6, the sum of the numbers in column C when the text in column B contains both “red” and “blue” in any order. Note that SUMIFS is not case-sensitive.
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 a criteria. The generic syntax for SUMIFS looks like this: 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 off with the sum_range, which is the numbers in the range C5:C16: Then we add the first range/criteria pair to test for “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: Again we use two asterisk (*) 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 in order 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:
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.
