AVERAGEIFS function

The AVERAGEIFS function in Excel calculates the average (arithmetic mean) of cells that meet multiple criteria.

Syntax

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• average_range: The range of cells to calculate the average from.
• criteria_range1: The first range of cells to evaluate with the corresponding criteria.
• criteria1: The first condition that must be met.
• criteria_range2, criteria2 (optional): Additional ranges and criteria (up to 127 range/criteria pairs).

Description

• AVERAGEIFS allows you to average values that satisfy multiple conditions across different ranges.
• The criteria1, criteria2, etc. can be numbers, expressions, cell references, or text strings (e.g., "=10", ">20", "Apple").
• Unlike AVERAGEIF, AVERAGEIFS handles multiple conditions and can apply those conditions to different ranges.

Example 1: Basic Usage

To calculate the average of numbers in A1:A5 that are greater than 10 and less than 20, use:

=AVERAGEIFS(A1:A5, A1:A5, ">10", A1:A5, "<20")

Suppose cells A1:A5 contain:

• A1 = 5
• A2 = 12
• A3 = 20
• A4 = 18
• A5 = 15

Result:

The function will return 15, because the numbers greater than 10 and less than 20 are 12, 18, 15:

$$\frac{12 + 18 + 15}{3} = 15$$

Example 2: Multiple Criteria Across Different Ranges

To calculate the average of numbers in B1:B5 where the corresponding values in A1:A5 are “Apple” and the values in C1:C5 are greater than 50, use:

=AVERAGEIFS(B1:B5, A1:A5, "Apple", C1:C5, ">50")

Suppose:

• A1:A5 contains: “Apple”, “Orange”, “Apple”, “Banana”, “Apple”
• B1:B5 contains: 10, 15, 20, 25, 30
• C1:C5 contains: 60, 40, 70, 80, 55

Result:

The function will return 25, because the values in B1:B5 that correspond to “Apple” and values in C1:C5 greater than 50 are 20, 30:

$$\frac{20 + 30}{2} = 25$$

Example 3: Using Cell References for Criteria

If you want to calculate the average of numbers in B1:B5 where the values in A1:A5 are greater than the value in D1 and less than the value in D2, use:

=AVERAGEIFS(B1:B5, A1:A5, ">" & D1, A1:A5, "<" & D2)

Wildcard Usage

You can use wildcards in criteria to match text:

• * (asterisk) matches any number of characters.
• ? (question mark) matches a single character.

For example, to average values in B1:B5 where the text in A1:A5 starts with “App”:

=AVERAGEIFS(B1:B5, A1:A5, "App*")

Handling Errors

• If no cells meet all the criteria, AVERAGEIFS returns #DIV/0!.
• Ensure all ranges are the same size, or the function will return a #VALUE! error.

Practical Use Cases

• AVERAGEIFS is helpful when you need to calculate averages based on multiple conditions, such as:
  • Calculating the average sales for a product in a certain price range and region.
  • Finding the average score for students who scored above a certain threshold in two different exams.

Important Notes

• Text and blank cells are ignored unless a condition specifically includes them.
• All criteria must be met for a cell to be included in the average.

AVERAGEIFS provides a powerful way to perform complex averaging operations with multiple conditions across different ranges.