How to Average Cells That Meet a Condition in Google Sheets

Use AVERAGEIF: =AVERAGEIF(A2:A100,"North",B2:B100) averages the values in column B only for rows where column A equals North.

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

First range holds the condition, the criterion is the test, and the last range holds the numbers to average.

How it works

AVERAGEIF averages only the numbers whose matching cell passes a test. The first range is checked against the criterion, and the average is taken from the third range on the rows that match. If you give just a range and a criterion, it averages that same range where the test is true.

Variations

Average where values exceed a number

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

With two arguments it averages the range itself on cells that pass the test — here, positive numbers only.

Match a cell as the criterion

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

D1 supplies the category, so you can change it without editing the formula.

Two or more conditions

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

AVERAGEIFS puts the average range first, then range/criterion pairs that must all be true.

Examples

Scenario	Formula
Average sales for the North region	`=AVERAGEIF(A2:A100,"North",B2:B100)`
Average of positive numbers only	`=AVERAGEIF(B2:B100,">0")`

FAQ

What is the difference between AVERAGEIF and AVERAGEIFS?

AVERAGEIF tests one condition; AVERAGEIFS tests several that must all be true, and it takes the average range as its first argument.

Does AVERAGEIF ignore blank cells?

Yes — blank and text cells in the average range are skipped, so they don't drag the result toward zero.

How do I average based on a date range?

Use AVERAGEIFS with two date tests: =AVERAGEIFS(B:B,A:A,">="&D1,A:A,"<="&D2).

Related formulas

→ See every Google Sheets formula in one cheat sheet