AVERAGEIF and AVERAGEIFS functions in Excel

AVERAGEIF averages the cells that meet one condition; AVERAGEIFS allows several conditions.

AVERAGEIF is to AVERAGE what SUMIF is to SUM. It answers "what is the average for this group?": the average mark of one class, the average bill for one city, the average delivery time for one courier.

Syntax

=AVERAGEIF(range, criteria, [average_range])

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Argument	What it is
`range`	The cells to test.
`criteria`	The condition, written as in SUMIF: "8A", ">50", or a cell reference.
`average_range`	The cells to average. In AVERAGEIF it comes last and may be left out. In AVERAGEIFS it comes first.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Marks of eight students in three subjects. One Science mark is missing (cell D6 is empty).

	A	B	C	D	E
1	Name	Class	Maths	Science	English
2	Asha	8A	78	85	91
3	Ravi	8B	45	52	60
4	Meena	8A	92	88	79
5	Kabir	8B	33	41	38
6	Tara	8A	67		72
7	Joy	8B	88	90	84
8	Neha	8A	54	61	58
9	Imran	8B	78	73	69

=AVERAGEIF(B2:B9,"8A",C2:C9)
Result: 72.75
The average Maths mark of the students in class 8A.
=AVERAGEIF(B2:B9,"8B",C2:C9)
Result: 61
And of class 8B.
=AVERAGEIF(C2:C9,">=50")
Result: 76.16666667
No third argument: the average of the Maths marks that are themselves 50 or more.
=AVERAGEIFS(C2:C9,B2:B9,"8A",E2:E9,">=70")
Result: 79
Two conditions: Maths average for 8A students with at least 70 in English.
=AVERAGEIF(B2:B9,"8C",C2:C9)
Result: #DIV/0!
No student is in 8C, so there is nothing to average: #DIV/0!.
=IFERROR(AVERAGEIF(B2:B9,"8C",C2:C9),"No students")
Result: No students
The same, with a message in place of the error.

How it works

AVERAGEIF tests each cell in the first range. For the rows that pass, it averages the matching cells of the third range. It is the same as dividing SUMIF by COUNTIF, in one function.

AVERAGEIFS takes several conditions and averages the rows that pass all of them.

The argument order changes

As with the SUM pair, the range to average is last in AVERAGEIF and first in AVERAGEIFS.

=AVERAGEIF(where_to_look, condition, what_to_average)
=AVERAGEIFS(what_to_average, where_to_look, condition, ...)

AVERAGEIFS works with a single condition too, so you can use it always and remember one order.

Conditions

They are written exactly as for SUMIF.

Condition	Meaning
`"8A"`	equal to the text 8A
`">=50"`	50 or more
`"<>0"`	not zero
`H1`	equal to the value in H1
`">"&H1`	greater than the value in H1
`"A*"`	text starting with A

Leaving out zeros

A common need is the average of the values that are not zero, because a zero means "no sale" or "not entered": =AVERAGEIF(F2:F13,"<>0"). With no third argument, the tested cells are the ones averaged.

The empty group

When no row meets the condition there is nothing to average, and the result is #DIV/0!. That is not a mistake in your formula. It is Excel saying it cannot divide by zero rows. In a report, wrap the formula in IFERROR to show a dash or a message, as in the last example.

Blanks in the range being averaged

Empty cells in the average range are skipped, as with AVERAGE. A student with a blank mark does not pull the group's average down. If blanks should count as zero, divide SUMIF by COUNTIF instead, or fill in the zeros.

A small summary table

List the classes in a column, say H2 downwards, and write once:

=AVERAGEIF($B$2:$B$9,H2,$C$2:$C$9)

Copy it down. The dollar signs hold the data ranges still while H2 moves to each class in turn.

Common mistakes

Arguments in the wrong order after switching between AVERAGEIF and AVERAGEIFS.
Ranges of different sizes. AVERAGEIFS returns #VALUE!. AVERAGEIF silently uses a shifted range.
Reading #DIV/0! as a fault, when it means the group is empty.
Comparison signs outside the quotation marks.

In Google Sheets

AVERAGEIF and AVERAGEIFS are the same in Google Sheets.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.