Excel: How to Use AVERAGEIFS with OR Condition

By default, the AVERAGEIFS function in Excel uses AND logic to calculate the average value of cells that meet multiple conditions.

If you instead would like to use the AVERAGEIFS function with OR logic to calculate the average value of cells that meet at least one of multiple conditions, you can use the following syntax:

=AVERAGE(IF((A2:A11="Guard")+(A2:A11="Forward"),B2:B11))

This particular formula finds the average of the values in the ranges B2:B11 where the corresponding value in the range A2:A11 is equal to Guard or Forward.

The following example shows how to use this formula in practice.

Example: How to Use AVERAGEIFS with OR Condition in Excel

Suppose we have the following dataset in Excel that shows the position and points scored by various basketball players:

Now suppose we would like to calculate the average value in the Points column for all players who have a value of either “Guard” or “Forward” in the Position column.

We can use the following formula to do so:

=AVERAGE(IF((A2:A11="Guard")+(A2:A11="Forward"),B2:B11))

The following screenshot shows how to use this formula in practice:

Excel AVERAGEIFS with OR condition

We can see that the average points scored by players with a position of Guard or Forward is 20.2857.

We can verify this is correct by manually calculating the average of all points values for players who are Guards or Forwards:

Average Points: (22 + 14 + 17 + 35 + 11 + 19 + 24) / 7 = 20.2857

This matches the value that we calculated using our formula.

Note: In this example, we calculated an average using two OR conditions but you can include more plus signs ( + ) in the formula to specify even more conditions.