FormuladrillAVERAGEIF
AVERAGEIF(range, criteria, [average_range]) averages the cells in average_range whose matching cell in range meets a condition, such as "North" or ">0". AVERAGEIFS(average_range, criteria_range1, criteria1, ...) does the same with several conditions. Both return #DIV/0! when no cell matches.
Syntax
AVERAGEIF(range, criteria, [average_range]) AVERAGEIFS(average_range, criteria_range1, criteria1, ...)| Part | What it is |
|---|---|
range | The cells to test. |
criteria | The condition: a value, cell reference or text like ">0" or "<>Cancelled". |
average_range | The numbers to average. Omit to average range itself. |
Examples
=AVERAGEIF(A2:A100, "North", C2:C100)Result: Average of C for rows where A is North
=AVERAGEIF(C2:C100, ">0")Result: Average of the positive values in C2:C100
Excludes zeros and negatives from both the sum and the count.
=AVERAGEIFS(C2:C100, A2:A100, "North", B2:B100, "Shipped")Result: Average of C for shipped orders from North
In AVERAGEIFS the average range comes first.
=IFERROR(AVERAGEIF(A2:A100, E2, C2:C100), 0)Result: 0 instead of #DIV/0! when the region in E2 has no rows
Common mistakes
- #DIV/0!: no cell met the condition, or the matching cells are all text or blank. Wrap in IFERROR if an empty result is acceptable.
- Argument order differs: AVERAGEIF has the average range last, AVERAGEIFS has it first.
- Blank cells in average_range are skipped, but cells containing 0 count. Use ">0" if zeros should be ignored.
- A criterion like ">" & E2 must be built with &; ">E2" is just text and matches nothing.
Practice AVERAGEIF
These drills check your answer on live data, free, no account needed.
- Average for one regionPut the average sale for the region named in E2 into E3.
Questions
How do I average excluding zeros?
=AVERAGEIF(C2:C100, "<>0") or, to exclude negatives too, =AVERAGEIF(C2:C100, ">0").
How do I average with two conditions?
Use AVERAGEIFS: =AVERAGEIFS(C2:C100, A2:A100, "North", D2:D100, ">=" & DATE(2026, 1, 1)).
Related
- SUMIFHow to use SUMIF in Excel to add up numbers that match one criterion, with examples for text, greater than, cell references and wildcards, and common fixes.
- COUNTIFHow to use COUNTIF in Excel to count cells equal to a value, above a number, containing text or not blank, with examples, a duplicate check and fixes.
- SUMIFSHow to use SUMIFS in Excel to add values that meet two or more conditions, with examples for region and status, date ranges, not-equal and wildcards.
- MAXIFS and MINIFSHow to use MAXIFS and MINIFS in Excel to find the highest or lowest value that meets conditions, with examples by region, status and date, plus the 2016 way.