Description
Use this function to calculate the average of numbers in a range that meet a single criterion.
AVERAGEIF is a powerful tool for calculating the average of a range based on a single condition, making it ideal for analyzing data based on a specific condition, such as averaging sales data for a particular product.
Syntax
AVERAGEIF(range, criteria, [average_range])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
range |
Yes | The range of cells to evaluate. | A cell reference, a cell range, or a formula which results in either of these. |
criteria |
Yes | The condition that must be met for a cell to be included in the average. | A number, a text string, a reference to a cell containing a number or text string, or a formula which results in any of these. |
average_range |
No | The range of cells to average. If omitted, the function uses range. |
A reference to a cell containing a number, a cell range containing numbers, or a formula which results in either of these. |
Example
Sample data
| A | B | C | D | |
|---|---|---|---|---|
1 |
Region | Product | Sales | Month |
2 |
North | Apples | 120 | January |
3 |
South | Oranges | 150 | January |
4 |
North | Bananas | 90 | February |
5 |
South | Apples | 200 | February |
6 |
North | Oranges | 85 | March |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Average a range of values that match a single condition in another column. | =AVERAGEIF(A2:A7, "North", C2:C7) |
Averages sales among the three rows where the region is "North". Result: ≈98.33 |
| Average a range of values that match a specific product. | =AVERAGEIF(B2:B7, "Apples", C2:C7) |
Averages sales among the two rows where the product is "Apples". Result: 160 |
| Average a range of values that match a specific month. | =AVERAGEIF(D2:D7, "February", C2:C7) |
Averages sales among the two rows from "February". Result: 145 |
| Average a range of values that match a single condition in another column. | =AVERAGEIF(A2:A7, "South", C2:C7) |
Averages sales among the two rows where the region is "South". Result: 175 |
| Average a range of values that match a specific product. | =AVERAGEIF(B2:B7, "Oranges", C2:C7) |
Averages sales among the two rows where the product is "Oranges". Result: 117.5 |
Notes
- Cells in
rangethat contain TRUE or FALSE are ignored. - If no cells meet the criteria, AVERAGEIF returns the #DIV/0! error value.
- Criteria can include wildcards like * (any number of characters) and ? (any single character).
- If
average_rangeis omitted, the function usesrangefor both criteria and values to average.
Tips
- Use AVERAGEIF to analyze data trends over a specific condition, such as product performance in a specific region.
- Combine AVERAGEIF with other functions such as SUMIF and COUNTIF for comprehensive data analysis.
- Use AVERAGEIF with conditional formatting to highlight data that meets specific criteria.