AVERAGEIF Generator
Average only the cells whose matching row meets a condition.
AVERAGEIF
Average only the cells whose matching row meets a condition.
Range tested against the criteria.
Condition to match, for example "West", ">100" or "<>0". Text and comparisons need quotes.
Numbers to average on matching rows. Excel treats this argument as optional, but do not leave the box empty - repeat the criteria range here when you test and average the same column. Give it the same size as the criteria range.
=AVERAGEIF(A2:A100, "West", C2:C100)Worked Example
Region names in column A and deal values in column C; you want the average deal size in the West region.
=AVERAGEIF(A2:A100, "West", C2:C100)Returns: Returns the mean of the column C values on rows where column A is West, ignoring blanks in C. If no row says West, it returns #DIV/0!.
Checks Before You Paste
- •AVERAGEIF returns #DIV/0! when no row matches, and also when the matching rows contain no numbers at all, because there is nothing to divide by. That is expected behaviour, not a broken formula, so wrap it in IFERROR if a dashboard needs a blank or a dash instead.
- •Blank cells in the average range are skipped rather than treated as zero, and so are cells holding TRUE or FALSE. The denominator is the count of matching rows that actually contain a number, which is why the result can differ from a hand-built SUM/COUNT.
- •If the average range is smaller than the criteria range, Excel silently stretches it from its top-left cell to match the criteria range's size and shape, quietly averaging cells you never named. Always enter both ranges at the same size; AVERAGEIFS rejects the mismatch outright instead of guessing.
How AVERAGEIF works
Availability: Excel 2007 and later on Windows and Mac, Excel for the web, Google Sheets and LibreOffice Calc. Before 2007 the same result needs SUMIF divided by COUNTIF.
AVERAGEIF takes the mean of the cells whose matching row satisfies one condition. Its arguments follow SUMIF's shape - the range being tested first, the criterion second, and the range being averaged last - and like SUMIF, that last argument can be left off, in which case the tested range is averaged directly. =AVERAGEIF(B2:B500, ">0") is the average of the positive values in B, with no second range needed.
What separates it from a hand-built SUM over COUNT is the denominator. AVERAGEIF divides by the number of matching rows that actually hold a number, not by the number of matching rows. Blank cells in the average range are skipped rather than treated as zero, and so are cells holding text or TRUE and FALSE. Six matching rows with two blanks give a mean over four values, which is usually what you want but is not what SUMIF/COUNTIF produces - that pair divides by all six and returns a smaller figure.
When nothing at all is left to divide by, the answer is #DIV/0!. That happens on two different occasions and it is worth telling them apart: either no row matched the criterion, or rows matched but the cells they point at contain no numbers. Both are honest reports rather than faults. If a dashboard needs a dash instead, wrap the call in IFERROR; if you need to know which of the two cases you are in, run the same criterion through COUNTIF first.
The criteria grammar is SUMIF's: a bare value for equality, quoted comparisons such as ">=100" and "<>Cancelled", the wildcards * ? and ~ in text, and an ampersand to join an operator to a cell reference. Text matching ignores case. The one behaviour to watch is the average range being resized: as with SUMIF, Excel takes only its top-left cell and stretches a block to the criteria range's shape, so a mismatch does not error - it averages cells you never named. AVERAGEIFS, by contrast, rejects mismatched ranges outright.
Syntax
=AVERAGEIF(range, criteria, [average_range])| Argument | Required | What it does |
|---|---|---|
range | Required | The cells the condition is tested against. Text, numbers or dates; blanks are never matched unless the criterion is "". |
criteria | Required | The condition. A number, a reference, or quoted text with a comparison operator and the wildcards * ? ~. Case-insensitive for text. |
average_range | Optional | The cells averaged. Omit it and the tested range is averaged. If given, only its top-left cell is honoured - Excel resizes it to the shape of range. |
More worked examples
A property list with postcodes in A2:A800 and sale prices in D2:D800, and you want the average price for anything in the SW postal district.
=AVERAGEIF(A2:A800, "SW*", D2:D800)Returns: The mean sale price across rows whose postcode starts with SW, over only the rows where D holds a number.
Rows where the price is still blank pending completion drop out of the average entirely rather than pulling it towards zero.
Response times in minutes in C2:C900, where a handful of rows record 0 for tickets that were closed on contact and would distort the mean.
=AVERAGEIF(C2:C900, ">0")Returns: The average response time across the tickets that took a measurable amount of time.
With one range and one criterion the third argument is unnecessary - the range being tested is the range being averaged.
A results sheet with cohort names in B2:B400 and marks in E2:E400, and a cohort name typed into H1 so the report can be repointed without editing formulas.
=IFERROR(AVERAGEIF(B2:B400, H1, E2:E400), "-")Returns: The cohort's average mark, or a dash when H1 names a cohort with no marks recorded yet.
IFERROR here is presentation, not a fix. If the dash is unexpected, check COUNTIF(B2:B400,H1) to see whether the criterion matched nothing or the marks column is empty.
Common mistakes
- #DIV/0! read as a broken formula
- It means there was nothing to average - no matching row, or matching rows with no numbers in the average range. The formula is behaving correctly; wrap it in IFERROR only once you know which of the two cases you are looking at.
- Expecting blanks to count as zero
- AVERAGEIF skips empty cells in the average range, so ten matching rows with three blanks give a mean over seven. If the blanks genuinely mean zero, fill them or switch to =SUMIF(...)/COUNTIF(...), which divides by every matching row.
- A mis-sized average range that still returns a number
- Excel resizes average_range from its top-left cell to the criteria range's shape, so pairing A2:A800 with D2:D400 averages D2:D800 anyway. No error appears - the figure is simply computed over rows you did not name.
- Zeros dragging the mean down
- A zero is a number, so it is included. Where zero means "not applicable" rather than a real measurement, exclude it explicitly with AVERAGEIFS on the same column, adding a ">0" or "<>0" pair.
- Numbers stored as text vanishing from the mean
- Text in the average range is skipped, so a column part-imported as text produces an average of only the genuinely numeric rows - plausible, and wrong. Compare =COUNT(D2:D800) with =COUNTA(D2:D800): a gap between them is the count of text rows.
Frequently Asked Questions
Not quite, and the difference is the denominator. COUNTIF counts every matching row; AVERAGEIF divides by the matching rows that hold a number. On data with blanks or text in the value column the two give different answers, and AVERAGEIF's is the one that matches what AVERAGE would do on the filtered rows.
Because the rows it matched contain no numbers to average - blanks, text, or values imported as text. The criterion is working; the average range is the problem. =COUNT(average_range) over the same rows will be 0.
AVERAGEIFS, which flips the argument order so the average range comes first and the criteria follow in pairs, exactly like SUMIFS. It also refuses mismatched range sizes instead of silently resizing them, which makes it the safer choice even for a single condition.
Only by expressing the outlier rule as a criterion, such as "<1000" against a known ceiling. For a proportional trim, TRIMMEAN drops a percentage from each end - =TRIMMEAN(D2:D800, 0.1) discards the top and bottom 5% - and takes no condition of its own.
No. Booleans in the average range are ignored, the same way text and blanks are, so they affect neither the total nor the count. This differs from AVERAGE applied to an array constant, where TRUE and FALSE are read as 1 and 0.