AVERAGEIFS
Returns the average of all cells that meet multiple criteria.
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.
What the function does
Overview
Returns the average of all cells that meet multiple criteria.
The AVERAGEIFS function calculates the arithmetic mean for cells that meet a specific set of multiple requirements. A critical technical distinction is the argument order: the 'ange' must be provided first, followed by pairs of criteria ranges and their associated conditions. All criteria are evaluated using AND logic, meaning a row is only included in the average if it satisfies every single condition defined. Criteria can utilize logical operators (>, <, <>, =) and wildcards; the asterisk (*) matches any sequence of characters while the question mark (?) matches any single character. Best practices dictate that all 'ange' arguments must possess the exact same dimensions (rows and columns) as the 'ange' to prevent calculation failures. The function automatically ignores empty cells and text values within the 'ange', but it will return an error if no cells meet the criteria or if the range contains only non-numeric data.
Quick reference
Syntax
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Inputs
Arguments
Example: C2:C100
Example: A2:A100
Example: "IT"
Example: B2:B100
Pattern: Criteria Pair
Formula patterns
Examples
average revenue for specific department and goal
=AVERAGEIFS(C2:C10, A2:A10, "Sales", B2:B10, ">5000")average scores excluding specific text and blanks
=AVERAGEIFS(B2:B20, A2:A20, "<>Pending", B2:B20, ">0")average values within a specific date range
=AVERAGEIFS(E2:E100, D2:D100, ">=1/1/2023", D2:D100, "<=12/31/2023")Avoid these issues
Common Errors
#DIV/0!
Cause: This occurs if no cells meet all the specified criteria, or if the average_range is empty or contains only text.
Fix: Verify that the criteria logic is correct and that the data being searched actually contains matching records.
#VALUE!
Cause: This happens when the criteria_range arguments do not have the same number of rows and columns as the average_range.
Fix: Ensure all range references (e.g., A2:A10 and C2:C10) have identical start and end points.
Platform support
Compatibility
Source: Microsoft Support
Common questions
Frequently Asked Questions
Returns the average of all cells that meet multiple criteria.
ange: The range to average ange1: Range for first condition criteria1: First condition ange2: Range for second condition
#DIV/0!: Verify that the criteria logic is correct and that the data being searched actually contains matching records. #VALUE!: Ensure all range references (e.g., A2:A10 and C2:C10) have identical start and end points.