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 |
|---|---|---|---|
| Region | Product | Sales | Month |
| North | Apples | 120 | January |
| South | Oranges | 150 | January |
| North | Bananas | 90 | February |
| South | Apples | 200 | February |
| North | Oranges | 85 | March |
Sample formulas
| Formula | Description | Result |
|---|---|---|
=AVERAGEIF(A2:A7, "North", C2:C7) |
Calculates the average sales for the North region. | 98.33 |
=AVERAGEIF(B2:B7, "Apples", C2:C7) |
Calculates the average sales for Apples. | 160 |
=AVERAGEIF(D2:D7, "February", C2:C7) |
Calculates the average sales for the month of February. | 145 |
=AVERAGEIF(A2:A7, "South", C2:C7) |
Calculates the average sales for the South region. | 175 |
=AVERAGEIF(B2:B7, "Oranges", C2:C7) |
Calculates the average sales for Oranges. | 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.
Related functions
AVERAGE
AVERAGEA
AVERAGEIF
AVERAGEIFS
LARGE
MAX
MAXA
MAXIFS
MEDIAN
MIN
MINA
MINIFS
PERCENTILE
PERCENTILE.EXC
PERCENTILE.INC
QUARTILE
QUARTILE.EXC
QUARTILE.INC
RANK
RANK.AVG
RANK.EQ
SMALL
STDEV
STDEV.P
STDEV.S
STDEVA
STDEVPA