Math

AVERAGEIFS

Definition

AVERAGEIFS returns the mean taken only from rows that meet every test you give it.

Syntax

=AVERAGEIFS(average_range, criteria_range1, criteria1, criteria_range2, criteria2)

Arguments

Argument	Meaning
`average_range`	The numbers to average when every test on that row passes, such as `C2:C5`.
`criteria_range1`	The first set of cells to check.
`criteria1`	The required value in that first set.
`criteria_range2`	The second set of cells to check.
`criteria2`	What the second set of cells must equal.

Worked example

A shop logs size codes, zone numbers, and unit counts in A2:C5.

A: Size	B: Zone	C: Units
S	2	10
S	1	20
M	2	8
S	2	14

Formula: =AVERAGEIFS(C2:C5,A2:A5,"S",B2:B5,2)

Result: 12. Size S and zone 2 appear together in rows 2 and 5, so (10 + 14) / 2 equals 12.

Practice

Cells A2:A5 hold 1, 1, 2, and 1. Cells B2:B5 hold 10, 20, 10, and 10. Cells C2:C5 hold 6, 8, 3, and 12. In D2, write a formula that averages C2:C5 where A2:A5 equals 1 and B2:B5 equals 10.

In D2, write an AVERAGEIFS formula that averages C2:C5 where A2:A5 equals 1 and B2:B5 equals 10.
D2 should show the mean of C2:C5 where A2:A5 equals 1 and B2:B5 equals 10.

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

AVERAGEIF takes the mean for one test. AVERAGE takes the mean of every number. AVERAGEIFS adds a second test before it averages.

Common mistake

Placing the numbers to average last, as AVERAGEIF does, for example =AVERAGEIFS(A2:A5,"S",B2:B5,2,C2:C5). AVERAGEIFS needs those numbers in the first argument.