How to Use AVERAGEIF in Google Sheets
=AVERAGEIF(criteria_range, criterion, [average_range]). If you leave out the average range, it averages the cells in the criteria range itself.
What AVERAGEIF does
AVERAGE gives the mean of everything in a range. AVERAGEIF gives the mean of only the rows that pass a test, such as the average score of one team or the average of all values above a threshold.
AVERAGEIF syntax
=AVERAGEIF(criteria_range, criterion, [average_range])
criteria_range: the cells that are tested.criterion: the condition. It can be an exact match ("Paid"or1), a comparison (">1",">=1","<1","<=1") or a not-equal test ("<>1","<>text").average_range(optional): the cells to average. If omitted, Sheets averagescriteria_range.
A step-by-step example
Here are five scores by team in cells A1:B6.
| A | B | |
|---|---|---|
| 1 | Team | Score |
| 2 | Sales | 82 |
| 3 | Support | 64 |
| 4 | Sales | 91 |
| 5 | Support | 77 |
| 6 | Sales | 58 |
To find the average score of the Sales team:
=AVERAGEIF(A2:A6, "Sales", B2:B6)
This returns 77. Here is why:
- Sheets finds the rows where column A is Sales: rows 2, 4 and 6.
- It averages the matching cells in column B: (82 + 91 + 58) / 3 = 77.
Test and average the same range
When the condition is about the numbers themselves, you can skip the third argument. To average only the scores above 60:
=AVERAGEIF(B2:B6, ">60")
This returns 78.5, the average of 82, 64, 91 and 77. To compare against a value in a cell, join the operator and the reference: "<"&F1.
Common AVERAGEIF mistakes
- Argument order. The tested range comes first and the range to average comes last.
- Missing quotation marks around text or operator criteria.
- Several conditions. AVERAGEIF takes one. For more, use AVERAGEIFS.
- No matching rows. If nothing meets the condition, there is nothing to average and the result is an error. Wrap it in IFERROR.
Try it yourself
Use the score table above (A1:B6). Type your formula into the box, starting with =, then press Check.
Exercise 1: Average by team
Write a formula that returns the average Score (column B) for the Sales team (column A).
Exercise 2: Average above 60
Write a formula that returns the average of the scores in B2:B6 that are greater than 60.
What to learn next
- SUMIFS: add up values that meet several conditions.
- COUNTIFS: count rows that meet several conditions.
- IF: run a test and return one of two results.
- AVERAGEIFS: averages values that meet several conditions.
Syntax and rules in this guide were checked against Google's AVERAGEIF help page. See all guides.