Spreadsheet formulas explained, side by side

AVERAGEIF Function in Excel, Google Sheets, Airtable & Notion

AVERAGEIF averages the numbers in the rows that meet a condition, for example the average order size in the East region, or the average of all non-zero values.

Syntax in each app

AppSupportSyntax & notes
Excel✔ Built inAVERAGEIF(range, criteria, [average_range])
AVERAGEIFS(average_range, criteria_range1, criteria1, ...) handles several conditions.
Google Sheets✔ Built inAVERAGEIF(criteria_range, criterion, [average_range])
Airtable✘ Not available—
Airtable formulas only see one record. Link the records to a summary record and add a rollup field with AVERAGE(values), then turn on “Only include linked records that meet certain conditions” to set the criteria. For a view, the summary bar plus a filter gives the same number.
Notion≈ Workaroundmean(prop("Orders").filter(current.prop("Region") == "East").map(current.prop("Amount")))
Filter a relation, pick the numbers with map, then take the mean. There is no #DIV/0!; check for an empty list with empty() if needed.
Try AVERAGEIF with your own values
One item per line
One value per line, or separate with commas
Try >100, <>0 or South (no match)
Result (as Excel & Google Sheets return it)
151.6667
Your formula, ready to paste
Excel=AVERAGEIF(A2:A7, "East", B2:B7) open excel.new ↗
Google Sheets=AVERAGEIF(A2:A7, "East", B2:B7) open sheets.new ↗
Airtable✘ No equivalent. Airtable formulas only see one record. Link the records to a summary record and add a rollup field with AVERAGE(values), then turn on “Only include linked records that meet certain conditions” to set the criteria. For a view, the summary bar plus a filter gives the same number.
Notion
workaround
mean(prop("Orders").filter(lower(current.prop("Region")) == "east").map(current.prop("Amount")))

Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.

Region	Amount
East	120
West	85
East	240
North	60
East	95
West	150

The Notion formula assumes a relation property Orders to a database with Region, Product and Amount properties. Text is compared in lower case to copy Excel's case-insensitive matching; wildcards become a test() regular expression.

Arguments

ArgumentWhat it means
rangeThe cells to test against the criteria.
criteriaThe condition, in quotes: "East", ">100", "<>Paid", "A*". A number or a cell reference also works. To use a cell in a comparison, join it: ">"&E1.
[average_range] optionalThe cells to average. If omitted, the cells in range are averaged.

Examples

FormulaResultWhyCopy
=AVERAGEIF(A2:A7, "East", B2:B7)151.6667Tester data: (120 + 240 + 95) / 3.
=AVERAGEIF(B2:B7, ">100")170No average_range: averages 120, 240 and 150.
=AVERAGEIF(B2:B7, "<>0")125The usual way to average while ignoring zeros (none here, so all six).
=AVERAGEIF(A2:A7, "South", B2:B7)#DIV/0!No row matches. Wrap in IFERROR to show a blank instead.

Examples use Excel / Google Sheets syntax unless noted.

How AVERAGEIF works

AVERAGEIF works like SUMIF but divides by the number of matching rows. Its most common job is excluding zeros: =AVERAGEIF(B2:B100, "<>0") averages only the cells that are not 0, which is what you want when a 0 means “no data yet”. Empty cells and text in the average range are already skipped, as in AVERAGE.

The argument order follows SUMIF: the range to test, the condition, then the cells to average. Criteria use the same rules: quoted operators, wildcards, case-insensitive text and ">"&E1 to compare with a cell. When no row matches, the result is #DIV/0!; =IFERROR(AVERAGEIF(...), "") hides it. For several conditions use AVERAGEIFS, which, like SUMIFS, takes the average range first.

Airtable has no AVERAGEIF; use a rollup field with AVERAGE(values) and a condition on which linked records to include. In Notion, filter the relation, map to the number and pass the list to mean().

Frequently Asked Questions

How do I average and ignore zeros?
Use =AVERAGEIF(B2:B100, "<>0"). Blank cells are ignored anyway.
How do I average with two conditions?
Use AVERAGEIFS(average_range, range1, criteria1, range2, criteria2).

Part of the Conditional Sums & Counts functions. Results in the tester follow Excel and Google Sheets behaviour; always double-check important work in your own file.