Math & aggregation
How to Use AVERAGEIF in Excel (Average with a Condition)
AVERAGEIF returns the average of the numbers in a range that meet a single condition: the average sale in the West region, the average score above 50, the average of all non-zero entries.
This guide covers text, numeric, and cell-reference criteria, how to average a different column than the one you test, and how to graduate to AVERAGEIFS when you have more than one condition.
AVERAGEIF syntax
=AVERAGEIF(range, criteria, [average_range]) | Argument | Required | What it does |
|---|---|---|
range | Required | The cells to test against the criteria. |
criteria | Required | The condition: "West", ">50", or a cell reference like E1. |
average_range | Optional | The cells to actually average. If omitted, AVERAGEIF averages the range it tests. |
Note: For more than one condition, use AVERAGEIFS, which puts the average_range first. Works the same in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Average by a text label
=AVERAGEIF(A:A, "West", B:B) Tests column A for "West" and averages the matching values in column B. Text criteria go in double quotes and are not case-sensitive.
2. Average values over a threshold
=AVERAGEIF(B:B, ">50") With a numeric comparison you can drop the average_range, so AVERAGEIF averages the range it is testing. The operator goes inside the quotes.
3. Average excluding zeros
=AVERAGEIF(B:B, "<>0") The "<>0" criteria means "not equal to zero", so blanks and zeros are skipped and only real values are averaged.
4. Two conditions with AVERAGEIFS
=AVERAGEIFS(C:C, A:A, "West", B:B, "Paid") AVERAGEIFS handles multiple conditions and puts the average_range (C) first, then each range/criteria pair. All conditions must be true (AND logic).
How to write AVERAGEIF step by step
- 1
Click the result cell and type =AVERAGEIF(
- 2
Select the range that holds the values to test (e.g. the region column), then a comma.
- 3
Type the condition in quotes ("West" or ">50"), then a comma.
- 4
Select the range of numbers to average (the average_range). Skip this if it is the same as the test range.
- 5
Close the bracket ) and press Enter. For multiple conditions, use AVERAGEIFS instead.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#DIV/0! | No cells match the criteria, so there is nothing to average. | Loosen the condition or wrap the formula in IFERROR, e.g. =IFERROR(AVERAGEIF(A:A,"West",B:B), 0). |
Result looks off | Blank cells in the average_range are skipped, but zeros are counted, which pulls the average down. | Exclude zeros with a "<>0" criteria, or test the value range itself instead of a label column. |
Operator ignored | A comparison like >E1 was written without joining the cell reference. | Concatenate the operator and reference with &, e.g. ">"&E1. |
#VALUE! | range and average_range are different shapes. | Make range and average_range the same size and orientation. |
Frequently asked questions
What is the difference between AVERAGEIF and AVERAGEIFS?
AVERAGEIF averages values that meet one condition. AVERAGEIFS handles multiple conditions and puts the average range first: =AVERAGEIFS(average_range, range1, criteria1, range2, criteria2).
Does AVERAGEIF ignore blank cells?
Yes. Empty cells in the average_range are skipped and do not count toward the average. Zeros, however, are included, so use a "<>0" criteria if you want to exclude them too.
How do I use AVERAGEIF with a date range?
Switch to AVERAGEIFS so you can set a start and end: =AVERAGEIFS(B:B, A:A, ">="&DATE(2026,1,1), A:A, "<"&DATE(2026,2,1)) averages January only.
Does AVERAGEIF work in Google Sheets?
Yes. The syntax is identical, including comparison operators inside quotes and the optional average_range argument.