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 *0*s. 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.