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

  1. Start with =SUMPRODUCT(
  2. Type of select the range that contains values E3:E11
  3. Use asterisk to product range values with condition range and open a parenthesis *(
  4. Enter criteria range – criteria condition in parenthesis (D3:D11=”Grass”)
  5. Type plus sign (+) to apply OR condition
  6. Enter other criteria range – criteria condition in parenthesis (D3:D11=”Water”)
  7. 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;

(D3:D11=”Grass”)+(D3:D11=”Water”) returns {1;1;1;0;0;0;1;1;1}

Finally; SUMPRODUCT with multiple criteria will return the sum of values in a 0/1 array.

=SUMPRODUCT(E3:E11*((D3:D11=”Grass”)+(D3:D11=”Water”))) returns 2497