AVERAGEIFS
Average the values whose row satisfies every one of a set of criteria.
Syntax
AVERAGEIFS(average_range, range1, criteria1, [range2, criteria2], ...)| Argument | Required | Description |
|---|---|---|
average_range | Required | The cells to average. First, unlike AVERAGEIF. |
range1 | Required | The first range to test. |
criteria1 | Required | The condition applied to it. |
Examples
Every example below was executed against VisiGrid engine 0.31.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=AVERAGEIFS(A1:A5,B1:B5,"East",A1:A5,">15") | 25 | East rows above 15 are 20 and 30. |
=AVERAGEIF(B1:B5,"East",A1:A5) | 20 | The same data with only the region condition. |
The range to average comes first
=AVERAGEIF(criteria_range, criteria, average_range)
=AVERAGEIFS(average_range, criteria_range, criteria)Reversed between the two, exactly as SUMIF and
SUMIFS are. Swapping them usually returns a number
rather than an error, so nothing looks wrong.
Using the plural form everywhere — even with one condition — removes the question permanently, and makes adding a second condition an append rather than a rewrite.
Excel compatibility
Matches Excel, including the average_range-first argument order.