How to Use AVERAGEIF in Google Sheets: Comprehensive Guide

AVERAGEIF returns the average of the values in a range that meet a condition. It is how you find the average order value for one region, the average score of one class, or the average of everything above a threshold.

Syntax

=AVERAGEIF(criteria_range, criterion, [average_range])

criteria_range: The cells to test against the criterion.
criterion: The condition a cell must meet.
average_range [Optional]: The cells to average. If omitted, AVERAGEIF averages the cells in criteria_range themselves.

Note the argument order: the test range is first, then the criterion, then the range to average, the same order as SUMIF.

Basic examples

With regions in A2:A100 and sales in B2:B100:

=AVERAGEIF(A2:A100, "North", B2:B100)

Returns the average sale in the North region.

=AVERAGEIF(B2:B100, ">0")

Averages only the positive numbers. Because there is no average_range, the same cells are tested and averaged. This is a handy way to ignore zeros.

=AVERAGEIF(B2:B100, ">=" & E1)

Averages the values at or above the number in E1.

Writing criteria

Exact text or number: "North", 5, or "=5"
Comparison: ">100", "<=50", "<>0"
Wildcards: "Wid*", "*corp", "?idget"
Blank / not blank: "" and "<>"
Operator plus a cell: ">" & E1

Text matching is not case-sensitive.

What gets ignored

AVERAGEIF averages only the numbers among the matching cells. Text and empty cells in average_range are skipped, rather than counted as zero. This is different from entering 0: a cell containing 0 is a number and does pull the average down.

The #DIV/0! error

If no cells meet the condition, there is nothing to average and AVERAGEIF returns #DIV/0!. Handle this with IFERROR:

=IFERROR(AVERAGEIF(A2:A100, E1, B2:B100), "No data")

Several conditions: AVERAGEIFS

AVERAGEIF takes one condition. For more, use AVERAGEIFS, where the average range comes first:

=AVERAGEIFS(B2:B100, A2:A100, "North", C2:C100, ">=" & DATE(2026, 1, 1))

This averages sales in the North region on or after January 1, 2026. Note the argument order differs from AVERAGEIF, just as SUMIFS differs from SUMIF.

Practical patterns

Average by category in a summary table

With category names in E2:E6:

=AVERAGEIF($A$2:$A$100, E2, $B$2:$B$100)

Drag it down to get an average for each category.

Average excluding a category

=AVERAGEIF(A2:A100, "<>Internal", B2:B100)

Average of values above or below the overall average

=AVERAGEIF(B2:B100, ">" & AVERAGE(B2:B100))

Common problems

#DIV/0!. No matching numeric cells. Check the criterion spelling, trailing spaces, and whether the average cells are numbers rather than text.
The result looks wrong. Zeros are included in the average. Use ">0" as a second criterion with AVERAGEIFS if you want to exclude them.
Misaligned ranges. criteria_range and average_range must be the same size and start on the same row. Misalignment shifts the matching.
Numbers stored as text. Text values in average_range are ignored, so the average can come from fewer cells than you think. Convert the data to numbers.
Weighted averages. AVERAGEIF treats every row equally. For a weighted average use SUMPRODUCT(values, weights) / SUM(weights).

AVERAGEIF vs. AVERAGE vs. QUERY vs. pivot table

Use AVERAGE for an unconditional average and AVERAGEIF for a single condition. For averages across many categories at once, a pivot table or QUERY with group by and avg() is simpler than a column of AVERAGEIF formulas.

Frequently asked questions

Is AVERAGEIF case-sensitive? No.

Does AVERAGEIF count blank cells as zero? No. Blank and text cells in the average range are ignored.

Can I use AVERAGEIF with dates? Yes: =AVERAGEIF(A2:A100, ">=" & DATE(2026, 1, 1), B2:B100).

What's the difference between AVERAGEIF and AVERAGEIFS? One condition versus several, and AVERAGEIFS takes the average range first.

Related Functions

AVERAGE: Average a set of numbers.
SUMIF: Sum cells that meet a condition.
COUNTIF: Count cells that meet a condition.
SUMIFS: Sum with several conditions.
QUERY: Group and average data with SQL-like syntax.

AVERAGEIF