AVERAGEIF in Google Sheets

Use AVERAGEIF to average only the cells that meet one condition — the mean sale for one region, the average score for one class.

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

Averages the values in B where the matching cell in A equals "North".

How it works

AVERAGEIF takes a criteria range, a condition, and an optional average range. It finds the rows where the criteria range meets the condition and averages the matching cells in the average range. If you leave out the average range, it averages the criteria range itself. Blank cells in the average range are skipped, so they do not drag the mean toward zero.

Variations

Average with a comparison

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

Averages only the values greater than 100 (no separate average range needed).

Two or more conditions (AVERAGEIFS)

=AVERAGEIFS(C2:C100, A2:A100, "North", B2:B100, ">0")

Note the argument order flips: the average range comes first in AVERAGEIFS.

Condition from a cell

=AVERAGEIF(A2:A100, E1, B2:B100)

Uses whatever region is typed in E1.

Examples

Scenario	Formula
Average grade for one class	`=AVERAGEIF(A2:A, "Class B", C2:C)`
Average of positive numbers only	`=AVERAGEIF(D2:D, ">0")`

FAQ

Why does AVERAGEIF give a #DIV/0! error?

No cells matched the condition, so there was nothing to average. Wrap it in IFERROR to show a friendly value, e.g. =IFERROR(AVERAGEIF(...),0).

What is the difference between AVERAGEIF and AVERAGEIFS?

AVERAGEIF handles one condition and puts the average range last. AVERAGEIFS handles many conditions and puts the average range first.

Does AVERAGEIF count blank cells as zero?

No. Blank cells in the average range are ignored, not treated as zero.

Related formulas

→ See all 34 Google Sheets formulas in one cheat sheet