How to sum values with OR operator using SUMPRODUCT with multiple criteria

The SUMIFS function can sum values based on multiple conditions. However, these conditions are all linked with AND operator. As a result, all conditions should be fulfilled to return a value. This article shows How you can sum values with multiple OR operators using SUMPRODUCT with multiple criteria.

Syntax

=SUMPRODUCT(value range * ((criteria range 1 = criteria 1) + (criteria range 2 = criteria 2) + …))

Steps

Start with =SUMPRODUCT(
Type of select the range that contains values E3:E11
Use asterisk to product range values with condition range and open a parenthesis *(
Enter criteria range – criteria condition in parenthesis (D3:D11="Grass")
Type plus sign (+) to apply OR condition
Enter other criteria range – criteria condition in parenthesis (D3:D11="Water")
Type )) to close function and finish the formula

How

The SUMPRODUCT function’s ability to handle arrays without array formulas provides a major advantage over the limitations of SUMIF and SUMIFS functions. The SUMPRODUCT can evaluate and return arrays from a range – value conditions. We can use this functionality to resolve criteria range – criteria pairs.

(D3:D11="Grass") returns {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Also, the SUMPRODUCT function sums the values in its argument array if there is only one argument. However, conditional equations returns Boolean values. So, they need to be converted into numbers for SUMPRODUCT to operate. Fortunately, the add operator (+) does this.

Another and more important outcome of the add operator (+) is to mimic the logical OR operator. TRUE OR FALSE statement returns TRUE. This behavior can be exploited by the add operator (+) in terms of 1 and 0s. If 1 and 0 represent TRUE and FALSE respectively, 1 + 0 = 1 equation acts like TRUE OR FALSE = TRUE statement as well. As a result;