The AVERAGEIFS function can calculate the average of values with multiple conditions. However, these conditions are linked with AND operator. But if you want to handle AVERAGE OR combination, AVERAGEIFS function will not work. This article shows how to calculate AVERAGE OR with multiple criteria using the SUMPRODUCT function.
Syntax
=SUMPRODUCT(value range * ((criteria range 1 = criteria 1) + (criteria range 2 = criteria 2) + …)) / SUMPRODUCT((criteria range 1 = criteria 1)+(criteria range 2 = criteria 2)+…)
Steps
Divide sum with OR formula by count with OR formula
=SUMPRODUCT(E3:E11*((D3:D11="Grass")+(D3:D11="Water"))) / SUMPRODUCT((D3:D11="Grass")+(D3:D11="Water"))
How
The SUMPRODUCT function can handle arrays without array formulas. This provides an advantage over the limitations of SUMIF and SUMIFS functions. The SUMPRODUCT can evaluate and return arrays from range – value conditions. We use this feature 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 which are meaningful for the SUMPRODUCT function. Fortunately, the add operator (+) does this.
Another and more important outcome of using the add operator (+) is to mimic the logical OR operator. In a logic, TRUE OR FALSE statement returns TRUE. This behavior can be reproduced with 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}
While summing these values returns the count of matched values, multiplying this array with the values array returns the total sum. Finally; dividing total sum by its count gives the average value which is how you can calculate AVERAGE with OR conditions.
=SUMPRODUCT(E3:E11*((D3:D11="Grass")+(D3:D11="Water")))/SUMPRODUCT((D3:D11="Grass")+(D3:D11="Water")) returns 416.2
Also see related articles how to sum values with OR operator using SUMPRODUCT with multiple criteria, and how to count values with OR operator.
