Arguments
range- The range tested against the criteria.criteria- The condition to match.average_range(optional) - The cells to average. If omitted, range is averaged.
AVERAGEIF
Averages the cells in a range that meet a condition.
Syntax
=AVERAGEIF(range, criteria, [average_range])=AVERAGEIF(A2:A50, "West", B2:B50)Returns: Average of B where A is West
Same shape as SUMIF, but returns the mean.
How AVERAGEIF evaluates
AVERAGEIF averages the cells that satisfy one condition. Its argument order is inherited from SUMIF and catches people out: the range being tested comes first, the criteria second, and the range actually averaged - if it is a different one - comes last. =AVERAGEIF(A2:A50, "West", B2:B50) tests column A and averages column B. Leave the third argument off and the tested range is also the averaged range, which is what you want for something like =AVERAGEIF(B2:B50, ">100").
The criteria argument is a small language rather than a value. A bare value means equals. A string beginning with an operator - ">100", "<=0", "<>" - is a comparison, and the whole thing including the operator must be inside quotation marks. To compare against a cell you have to build the string: ">" & E1, not ">E1". Text criteria are matched case-insensitively, so "west" and "WEST" find the same rows, and they accept the wildcards * for any run of characters and ? for exactly one, with ~ escaping a literal asterisk or question mark.
Two limits matter. AVERAGEIF takes exactly one condition; the moment you need two, switch to AVERAGEIFS, whose argument order is reversed - the averaged range comes first there. And when nothing matches, AVERAGEIF returns #DIV/0! rather than 0 or a blank, because dividing by a count of zero is undefined. That is the correct answer, but on a dashboard it usually wants wrapping in IFERROR so an empty region reads as a dash instead of an error.
- Shape of average_range
- Excel takes only the top-left cell of average_range and then resizes it to match range. A mistyped =AVERAGEIF(A2:A50, "West", B2:B10) therefore averages B2:B50 without complaint. Always give both ranges the same height.
- What gets averaged
- Only numeric cells among the matched rows. If a row matches the criteria but its average_range cell is blank or text, that row is excluded from the mean entirely - it does not count as zero.
- Criteria coercion
- "100" and 100 match the same cells; the criteria string is read as a number when it looks like one. Blank criteria matches empty cells, and "<>" matches every non-empty cell.
- Case and wildcards
- Text matching ignores case. * and ? are always active in text criteria, so "S*" matches South and Southwest alike; use "~*" to match a literal asterisk.
- Dates in criteria
- Dates are serial numbers, so ">=" & DATE(2024,1,1) works while ">=01/01/2024" is fragile - it depends on the machine's regional date settings.
- One condition only
- There is no way to AND two tests inside AVERAGEIF. AVERAGEIFS handles that, with the averaged range moved to the front of the argument list.
Errors AVERAGEIF returns
| Error | What it means and how to fix it |
|---|---|
| #DIV/0! | No cell matched, or every matching row had a non-numeric cell in average_range. Check for trailing spaces in the tested column with TRIM, and check that the criteria string is quoted correctly. Wrap in IFERROR when an empty result is legitimate. |
| #VALUE! | In Excel this usually means range and average_range sit on a closed external workbook, or the criteria is itself an error value. Open the source workbook, or fix the cell the criteria references. |
| #NAME? | The criteria was written without quotation marks, as in =AVERAGEIF(A2:A50, West, B2:B50), so Excel read West as a defined name. Quote text criteria. |
| #REF! | One of the two ranges no longer exists after a deletion, or an average_range on another sheet was removed. Re-point the reference. |
More examples
=AVERAGEIF(C2:C500, ">=" & TODAY()-30, D2:D500)Returns: Average value of the last 30 days of records
The operator is concatenated onto a live date. Writing ">=TODAY()-30" in quotes would compare against the literal text instead.
=AVERAGEIF(A2:A200, "*Ltd", B2:B200)Returns: Average invoice for companies whose name ends in Ltd
A trailing wildcard. Matching is case-insensitive, so ltd and LTD are included too.
=IFERROR(AVERAGEIF(A2:A50, F2, B2:B50), "-")Returns: The regional average, or a dash
F2 holds the region name, so the formula can be copied down a summary table. IFERROR turns the #DIV/0! of an unused region into something readable.
Compatibility
| Application | Support |
|---|---|
| Excel 2007-2016 | Supported - AVERAGEIF and AVERAGEIFS were both introduced in Excel 2007. |
| Excel 2019 | Identical behaviour. |
| Excel 2021 / Microsoft 365 | Identical. In 365 many people use =AVERAGE(FILTER(...)) instead, which allows any expression as the condition. |
| Google Sheets | Same syntax and same #DIV/0! on no match. Sheets applies wildcards the same way but is stricter about locale-formatted date text. |
| LibreOffice Calc | Supported. Whether * and ? act as wildcards depends on the setting in Tools > Options > Calc > Calculate, which can be set to regular expressions instead. |
Does AVERAGEIF Work in Google Sheets?
Yes. AVERAGEIF uses the same syntax in Google Sheets and LibreOffice Calc, so you can paste these formulas straight across.
In the ExcelTool editor
The ExcelTool editor implements AVERAGEIF with two or three arguments and uses the same conditional-aggregate machinery as SUMIF, COUNTIF and AVERAGEIFS, so the criteria language is identical across all of them.
That criteria parser supports the <, <=, >, >=, <> and = operators, plain equality with Excel's loose typing so "100" and 100 match the same cells, and the * and ? wildcards with ~ as the escape character; text comparison is case-insensitive.
It differs from Excel deliberately on shape: the editor requires average_range to have exactly the same number of rows and columns as range and returns #VALUE! when it does not, instead of silently resizing from the top-left cell the way Excel does.
Among the matched cells only numbers are averaged, an error value in the averaged range is returned as the result, and no matches at all gives #DIV/0!.
You can try any of these formulas in the free in-browser spreadsheet editor - it opens .xlsx files, recalculates as you type, and needs no signup.
AVERAGEIF questions
Because they count different things. COUNTIF counts every matching row, including ones whose value cell is blank or text; AVERAGEIF divides only by the matching rows that had a number to average. If some matched rows have an empty value cell, SUMIF/COUNTIF is effectively treating those blanks as zeros and reports a lower mean. AVERAGEIF's answer is the mean of the observations that exist.
Not with AVERAGEIF - it accepts one condition only. Use =AVERAGEIFS(B2:B50, A2:A50, "West", B2:B50, "<>0"), which tests column B twice: once through the region and once against zero. Note the reversed argument order: the averaged range moves to the front.
Almost always trailing whitespace on one side or the other. Compare with =COUNTIF(A2:A50, F2) - if that is 0 too, the strings genuinely do not match. Test =LEN(A2) against the visible character count to spot a stray space, and clean the column with TRIM. Non-breaking spaces from web pastes survive TRIM and need SUBSTITUTE(A2, CHAR(160), "").
No. Omitting it averages the tested range itself, which is what you want for =AVERAGEIF(B2:B100, ">0"). Passing the same range explicitly as the third argument is legal and does exactly the same thing.