AVERAGEIF()
Averages the cells in a range that meet a single condition.
AVERAGEIF(range, criteria, [average_range])AVERAGEIF works like SUMIF, but divides by the count of matches instead of returning the total. It ignores non-matching cells entirely — they don't count toward the average or its denominator.
If no cells match criteria, AVERAGEIF returns #DIV/0!.
AVERAGEIF inherits SUMIF's inconsistent argument order — criteria range first, average range last — so AVERAGEIFS is usually the better choice even for one condition. The failure worth anticipating is an empty result: if no row matches, AVERAGEIF returns #DIV/0! rather than zero or blank, which is correct behaviour but unhelpful in a report. Wrap it in IFERROR with a plain-English fallback, or guard it with COUNTIF, so a category with no activity reads as "No data" rather than an error somebody will paper over.
Arguments
- range
- The range to test against criteria.
- criteria
- The condition that decides which cells to include.
- average_rangeoptional
- The range to average when criteria matches. Defaults to range if omitted.
Examples
=AVERAGEIF(A2:A20, "North", C2:C20)Averages column C only where column A says North.
=AVERAGEIF(C2:C200, "<>0", C2:C200)Excludes zeros from the average — the common case where 0 was entered to mean not recorded rather than a genuine zero.
=AVERAGEIFS(D2:D200, A2:A200, "North", B2:B200, ">="&DATE(2026,1,1))AVERAGEIFS takes the average range first and allows several conditions — the opposite argument order to AVERAGEIF, which catches everyone once.
Notes and gotchas
- Argument order is
AVERAGEIF(range, criteria, average_range)— the tested range first, matchingSUMIFrather thanAVERAGEIFS. AVERAGEIF(range, "<>0")is the idiomatic way to average while excluding zeros, which is usually what people mean when a mean looks too low.- Operator criteria must be text:
">100", or">"&B1to compare against a cell. - Blanks and text in
average_rangeare skipped, so the denominator may be smaller than the number of matching rows. - Returns
#DIV/0!rather than 0 when nothing matches — a meaningful distinction, since an average of no values is undefined rather than zero.
Common errors
| You see | Because | Fix |
|---|---|---|
| #DIV/0! | No cells matched the criteria, or all matching cells are non-numeric. | Check with COUNTIF using the same criteria; zero confirms it is a matching problem. |
AVERAGEIF tricks, tips, and workbook hacks
AVERAGEIF calculates the arithmetic mean of cells that satisfy a single criterion—such as average deal size for one sales rep, average cycle time for completed orders, or average unit cost excluding zero-cost promotional items.
Its single most practical trick is excluding zeroes from an average (=AVERAGEIF(B2:B100, "<>0")) when unpopulated months or placeholder rows contain 0.00 that would otherwise drag the simple AVERAGE down.
Quick AVERAGEIF checks before trusting the result
- Use =AVERAGEIF(B2:B100, ">0") to average only active months and ignore 0 placeholders.
- Wrap in IFERROR(..., 0) or IFERROR(..., "No data") because AVERAGEIF returns #DIV/0! whenever zero rows match the criterion.
- Double-check that range and average_range start on the exact same row—AVERAGEIF shares SUMIF's silent range-resizing quirk!
| AVERAGEIF pattern | AVERAGEIF formula | Best use | Watch out |
|---|---|---|---|
| Average while ignoring zero placeholders | =AVERAGEIF(B2:B100,"<>0") | Monthly forecast templates where future months show $0 until actuals are booked. | Omit the 3rd argument when the criteria range and averaged range are the same column. |
| Average excluding #N/A or errors in a single column | =AVERAGEIF(B2:B100,"<9.99E+307") | Averaging a calculated lookup column where unmatched rows return #N/A. | Any finite number is less than 9.99E+307, while error cells fail the test and are skipped. |
| Average values above the overall median | =AVERAGEIF(B2:B100,">"&MEDIAN(B2:B100)) | Measuring upper-half performance without extreme bottom-end noise. | Concatenate ">" with MEDIAN() using &. |
| AVERAGEIF mistake | What happens in AVERAGEIF | Fix |
|---|---|---|
| Zero matches throwing #DIV/0! into executive dashboards | Selecting a new territory with 0 closed deals turns the Average Deal Size KPI card into #DIV/0!. | Wrap with IFERROR(AVERAGEIF(...), 0). |
| Misaligned average_range start row | =AVERAGEIF(A2:A100, "East", C1:C100) averages C1:C99, shifting every matched row by 1. | Lock both ranges to identical row boundaries ($A$2:$A$100 and $C$2:$C$100). |
AVERAGEIF practice drills
- Create a 12-month sales column where months 1–6 have sales and months 7–12 have 0. Compare =AVERAGE(B2:B13) against =AVERAGEIF(B2:B13, ">0").



