Where data is an Excel Table in the range B5:F16. The result is $205, the sum of Total where the Color is “Red” OR “Blue”. Note the SUMIFS function is not case-sensitive.
SUMIFS + SUMIFS
Before looking at the more advanced option shown in the example, it’s important to note that we can solve this problem with two calls to SUMIFS like this: The first SUMIFS sums Total where the color is “Red”, and the second SUMIFS sums Total where the color is “Blue”. By adding the two functions together in one formula, we effectively get a sum of Total where the color is either “Red” or “Blue”.
SUMIFS + array constant
A more elegant solution is to give the SUMIFS function more than one value for the criteria, in an array constant. To do this, construct a normal SUMIFS formula, but supply criteria in array syntax like this: Placing the array constant inside SUMIFS as criteria1, we have: In this configuration, SUMIFS will return two sums: one for totals where the color is “Red” and one for totals where the color is “Blue”. These results will be returned in an array like this: In the latest version of Excel you will see these results spill onto the worksheet into two cells. However, we don’t want two results on the worksheet (we want a single result) so we wrap the SUMIFS function inside the SUM function like this: Now SUMIFS will return the array to SUM: and the SUM function will return the final result, 205.
Criteria as reference
You can also supply criteria as a cell reference instead of an array constant. For example, with “Red” in cell A1 and “Blue” in cell A2, you can use a formula like this: In the latest version of Excel, this formula will work as-is, with no special handling. In Legacy Excel, you will need to enter as an array formula with control + shift + enter.
SUMPRODUCT alternative
You can also use the SUMPRODUCT function to sum cells with OR logic with a formula like this This formula uses the MATCH function and the ISNUMBER function to create a Boolean array that is then multiplied by data[Total] to get a final result. This is a flexible approach that works nicely in situations where SUMIFS can’t be used. Note: in the current version of Excel you can use the SUM function instead of SUMPRODUCT. See Why SUMPRODUCT?
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.
