How to Use the AVERAGEIFS Function in Google Sheets
=AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...]).
What AVERAGEIFS does
AVERAGEIF handles one condition. AVERAGEIFS handles several, such as "the average sales for the East region where quantity is above 1". A row counts only when it meets all of the conditions. It is the averaging cousin of SUMIFS and COUNTIFS.
AVERAGEIFS syntax
=AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
average_range: the range to average.criteria_range1: the range checked againstcriterion1.criterion1: the condition applied to that range.criteria_range2, criterion2, ...: optional further pairs.
The average range comes first here, which is the opposite of how some people expect it. Google's examples include AVERAGEIFS(A1:A10, B1:B10, ">20", C1:C10, "<30").
A step-by-step example
Here are four rows of region, sales and quantity in cells A1:C5.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Sales | Qty |
| 2 | East | 10 | 1 |
| 3 | West | 20 | 2 |
| 4 | East | 30 | 3 |
| 5 | West | 40 | 4 |
=AVERAGEIFS(B2:B5, A2:A5, "East")
This returns 20, the average of the East sales 10 and 30.
=AVERAGEIFS(B2:B5, A2:A5, "East", C2:C5, ">1")
This returns 30. Here is why: only the East row with quantity 3 meets both conditions, and the average of one value is that value.
Common mistakes
- Putting the average range last. It comes first.
- Ranges of different sizes. Keep every range the same length.
- Forgetting quotation marks around text and comparison conditions.
Try it yourself
Use the table above (A1:C5). Type your formula into the box, starting with =, then press Check.
Exercise 1: East average
Write a formula that averages B2:B5 for the rows where the region in A2:A5 is East.
Exercise 2: Two conditions
Write a formula that averages B2:B5 for East rows where the quantity in C2:C5 is greater than 1.
What to learn next
- SUBTOTAL: total that respects filters and hidden rows.
- AVERAGEIF: average the values that meet a condition.
- SUMIFS: add up values that meet several conditions.
- MAXIFS: the largest value that meets conditions.
Syntax and rules in this guide were checked against Google's AVERAGEIFS help page. See all guides.