AVERAGEIF calculates the arithmetic mean of numeric values that meet one condition. The condition can apply to the values themselves or to a corresponding label column.
Use it for questions such as average sales in one region or average amounts above a threshold. Zero values count when they qualify; empty cells are not numeric observations.
AVERAGEIF function syntax
=AVERAGEIF(criteria_range, criterion, [average_range])- criteria_range: the cells tested against the condition.
- criterion: the required label, comparison, or cell reference.
- average_range: corresponding values to average; omitted means average the criteria range.
The Google AVERAGEIF reference describes the function arguments.
Example data
Enter this dataset starting in A1. Leave cells marked “leave empty” empty; type any displayed formula as a formula. Keep a separate blank area for the results.
| Region | Sales | Product |
|---|---|---|
| East | 200 | Pro Camera |
| West | 120 | Mouse |
| east | 300 | Pro Keyboard |
| North | 80 | Cable |
| East | 100 | Webcam Pro |
| West | 0 | Cable |
The examples below use this dataset unless the formula supplies its own values. Array results need enough empty cells to expand. The formulas use commas as argument separators. Your spreadsheet locale may require semicolons.
Average sales for one region
East sales are 200, 300, and 100, so their mean is 200. Matching is case-insensitive, and the lowercase east row is included.
=AVERAGEIF(A2:A7,"East",B2:B7)Result: 200
Reference A2 to take the criterion from a cell instead of typing it into the formula.
=AVERAGEIF(A2:A7,A2,B2:B7)Result: 200
Average values above a threshold
Omitting the average range tests and averages the sales numbers themselves. Only values greater than 100 qualify.
=AVERAGEIF(B2:B7,">100")Result: 206.6666667
A >0 criterion excludes the recorded zero when that matches the intended analysis.
=AVERAGEIF(B2:B7,">0")Result: 160
Average records containing text
The *Pro* pattern matches a product name containing Pro anywhere.
=AVERAGEIF(C2:C7,"*Pro*",B2:B7)Result: 200
The Pro* pattern requires the name to start with Pro, excluding Webcam Pro.
=AVERAGEIF(C2:C7,"Pro*",B2:B7)Result: 250
Exclude a category
The non-East values are 120, 80, and 0. All three numbers count, giving approximately 66.67.
=AVERAGEIF(A2:A7,"<>East",B2:B7)Result: 66.66666667
Handle a condition with no numeric matches
No record matches Missing, so there are no qualifying numbers to divide by. The result is #DIV/0!.
=AVERAGEIF(A2:A7,"Missing",B2:B7)Result: #DIV/0!
Require multiple conditions
AVERAGEIFS averages East sales above 100, producing 250 from 200 and 300. Its average range is the first argument.
=AVERAGEIFS(B2:B7,A2:A7,"East",B2:B7,">100")Result: 250
Other Google Sheets articles you may also like