📊 Statistical Function · intermediate
AVERAGEIFS Function in Google Sheets
Returns the average of values that meet multiple conditions.
Syntax
AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])Returns: The mean of average_range values where ALL conditions match.
Excel equivalent: AVERAGEIFS (identical)
Parameters
| Name | Required | Description |
|---|---|---|
| average_range | Required | The range to average. |
| criteria_range1 | Required | The first range to test. |
| criterion1 | Required | The first condition. |
| criteria_range2, criterion2, ... | Optional | Additional pairs (AND logic). |
Examples
Q1 average for one customer
=AVERAGEIFS(C:C, A:A, "Acme Corp", B:B, ">="&DATE(2026,1,1), B:B, "<"&DATE(2026,4,1))Mean of C for Acme Corp Q1 2026.
Sale for one product in one region
=AVERAGEIFS(D:D, A:A, "Apple", B:B, "West")Mean of D where A is Apple AND B is West.
Excluding outliers
=AVERAGEIFS(B:B, B:B, ">0", B:B, "<10000")Mean excluding zeros and values over 10000.
When to use an alternative
- AVERAGEIF — Only one condition.
- QUERY with AVG WHERE — Grouped averages.
Common errors and how to fix them
#DIV/0!
Cause: No rows match all criteria.
Fix: Test with COUNTIFS. Wrap with IFERROR.
#VALUE!
Cause: Mismatched dimensions.
Fix: All ranges same size.
Related functions
AVERAGEIF
Returns the average of values in a range that meet a single condition. The conditional version of AV...
SUMIFS
Sums values that meet multiple conditions. The multi-criteria version of SUMIF, and the cleanest way...
COUNTIFS
Counts cells that meet multiple conditions across multiple ranges. The multi-criteria version of COU...
QUERY
Runs a SQL-like query against a range. Google Sheets' most powerful function for filtering, sorting,...
AVERAGE
Returns the arithmetic mean of numeric values in a range. Ignores text and blanks but treats boolean...
Frequently Asked Questions
OR conditions?
No — all AND. Use QUERY for OR.
Why different argument order from AVERAGEIF?
AVERAGEIFS puts average_range FIRST. Same gotcha as SUMIF vs SUMIFS.