How can I use the AVERAGEIFS function in Excel with an OR condition?

By /

The AVERAGEIFS function in Excel allows users to find the average value of a range of cells based on multiple criteria. This function can also be used with an OR condition, where the average is calculated if any of the specified criteria are met. To use this feature, simply include the OR condition within the function, separating each criterion with a comma. This enables users to efficiently analyze and calculate the average of data that meets one or more specific criteria, providing a more comprehensive and accurate result.

Excel: 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.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

x

Related terms:

  1. Excel: Use AVERAGEIFS with OR Condition
  2. What is the Excel IF function with a greater than or equal to condition?
  3. VBA: Write AVERAGEIF and AVERAGEIFS Functions ## rewrite this title as a student question
  4. How to use AVERAGEIFS in Google Sheets (3 Examples)
  5. How can I use the “when” function in PySpark to include an AND condition?
  6. How can I use the PySpark “when” function with an OR condition?
  7. How can I use the IF Function with the LEFT Function in Microsoft Excel?
  8. How can I update column values based on a condition in PySpark?
  9. How can I sum a column in PySpark based on a condition?
  10. How can I count the values in a column in PySpark, while also applying a condition on the column’s values?