Conditional formula page
AVERAGEIF Formula
Use AVERAGEIF when one criteria range controls which numbers should be averaged.
Average values by one condition with AVERAGEIF.
With the sample data, this averages Amount values for East rows.
Copy formulas
=AVERAGEIF(B2:B100, "East", D2:D100)=AVERAGEIF(B2:B100, "East", D2:D100)Excel and Google Sheets use AVERAGEIF for one condition. Matching numeric values are averaged while text and blank cells in the average range are ignored.
Example data
| Date | Region | Product | Amount | Rep |
|---|---|---|---|---|
| 2026-01-04 | East | Widget | 420 | Maya |
| 2026-01-12 | West | Widget | 310 | Noah |
| 2026-02-03 | East | Gadget | 275 | Maya |
| 2026-02-15 | East | Widget | 640 | Iris |
With the sample data, this averages Amount values for East rows.
How the formula works
- B2:B100 chooses matching rows.
- D2:D100 supplies the values to average.
- Blank and nonnumeric values in the average range are ignored by spreadsheet rules.
| Syntax piece | Role in the formula |
|---|---|
| B2:B100 | The criteria range containing Region or another category. |
| "East" | The criterion selecting the rows to average. |
| D2:D100 | The average range containing the numeric values. |
| Non-numeric values | Text and blanks in the average range are ignored; numeric zero values are included. |
Verified examples
=AVERAGEIF(B2:B100, "East", D2:D100)Excel: Use the sample sales table with East amounts averaging 445. Returns: 445
=AVERAGEIF(B2:B100,F2,D2:D100)Google Sheets: Enter West in F2; the matching Amount values average 310. Returns: 310
=AVERAGEIF(C2:C100,"Widget",D2:D100)Excel: Use Widget in C2:C100; matching Amount values average 456.67 when rounded to two decimals. Returns: 456.67 (rounded to two decimals)
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| AVERAGEIF returns #DIV/0! | No matching rows contain numeric values in the average range. | Check the criterion and convert matching values to numbers, or wrap the formula in IFERROR when no match is a valid state. |
| The average uses values from the wrong rows | The criteria range and average range have different row boundaries. | Align both ranges, such as B2:B100 and D2:D100. |
| Zeros change the result but blanks do not | A numeric zero is a real value and participates in the average, while blanks and text are ignored. | Decide whether zero represents a measured value; remove or transform it before averaging if it should be excluded. |
When not to use this formula
- Do not use AVERAGEIF when several independent conditions must be applied; use AVERAGEIFS instead.
Alternatives
| Alternative | When to use it |
|---|---|
| AVERAGEIFS Formula | Use when the average must satisfy multiple conditions. |
| SUMIF Formula | Use when the matching numeric values should be totaled rather than averaged. |
Related formulas
Official references
- AVERAGEIF function from Microsoft
FAQ
Are blanks and text included in the average?
No. AVERAGEIF ignores blanks and text in the average range, but it includes numeric zeros.
When should I use AVERAGEIFS?
Use AVERAGEIFS when the average must satisfy two or more criteria, such as Region plus Product.