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
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | AVERAGEIF(range, criteria, [average_range])AVERAGEIFS(average_range, criteria_range1, criteria1, ...) handles several conditions. |
| Google Sheets | ✔ Built in | AVERAGEIF(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 | ≈ Workaround | mean(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. |
| 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
| Argument | What it means |
|---|---|
range | The cells to test against the criteria. |
criteria | The 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] optional | The cells to average. If omitted, the cells in range are averaged. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=AVERAGEIF(A2:A7, "East", B2:B7) | 151.6667 | Tester data: (120 + 240 + 95) / 3. | |
=AVERAGEIF(B2:B7, ">100") | 170 | No average_range: averages 120, 240 and 150. | |
=AVERAGEIF(B2:B7, "<>0") | 125 | The 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).