Math

AVERAGEIF

Definition

AVERAGEIF returns the mean of the numbers whose matching cells pass one test.

Syntax

=AVERAGEIF(range, criteria, [average_range])

Arguments

Argument	Meaning
`range`	The cells compared with the criterion, such as `A2:A5`.
`criteria`	The value that counts as a match. Type a number as is; wrap text in double quotes.
`average_range`	Optional. The cells to average when the matching cell in range passes the test. If left out, Excel averages range itself.

Worked example

A depot logs region names and crate counts. Regions are in A2:A5 and crate counts are in B2:B5.

A: Region	B: Crates
north	8
south	20
north	12
north	4

Formula: =AVERAGEIF(A2:A5,"north",B2:B5)

Result: 8. The north rows are 8, 12, and 4, and (8 + 12 + 4) / 3 equals 8.

Practice

Cells A2:A5 hold east, west, east, and east. Cells B2:B5 hold 10, 20, 14, and 6. In C2, write a formula that averages B2:B5 for rows where A2:A5 equals east.

In C2, write an AVERAGEIF formula that averages B2:B5 where A2:A5 equals east.
C2 should show the mean of B2:B5 where A2:A5 equals east.

Step 1 of 2

Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.

Loading the sheet…

Related functions

AVERAGE takes the mean of every number with no test. COUNTIF counts the cells that pass a test. AVERAGEIF keeps one test and returns the mean.

Common mistake

Swapping the first and last ranges, as in =AVERAGEIF(B2:B5,"north",A2:A5), so the formula tests the crate counts and tries to average the region names.