Excel AVERAGEIF/AVERAGEIFS Functions

last modified October 5, 2026

The AVERAGEIF and AVERAGEIFS functions calculate an average from only those cells that satisfy one or more conditions. AVERAGEIF applies a single condition, while AVERAGEIFS applies several conditions at once. This tutorial covers their syntax, the difference in argument order, criteria with operators, wildcards, and dates, plus the rules Excel uses when the average range does not line up with the criteria range.

AVERAGEIF/AVERAGEIFS Function Basics

AVERAGEIF averages the cells that meet a single criterion, while AVERAGEIFS averages the cells that meet every one of several criteria (AND logic). Both return #DIV/0! when no cell satisfies the condition.

Function	Description	Syntax
AVERAGEIF	Averages cells that meet one condition	=AVERAGEIF(range, criteria, [average_range])
AVERAGEIFS	Averages cells that meet all conditions	=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)

This table compares both functions. Note the different argument order between them. AVERAGEIFS takes the average range first and then pairs of criteria ranges and criteria, while AVERAGEIF puts the criteria range first and the average range last, where it is optional.

Argument	Meaning
range / criteria_range	The cells tested against the criteria. `AVERAGEIF` has one range; `AVERAGEIFS` takes one criteria range per condition. Every criteria range must have the same dimensions as the average range.
criteria	The condition to satisfy. It can be a number (`400`), text (`"East"`), a comparison (`">400"`), a wildcard pattern (`"Pro*"`), or a reference joined to an operator (`">"&D1`). Text criteria are not case-sensitive and must be quoted.
average_range	The cells that are actually averaged. In `AVERAGEIF` it is optional and comes last; in `AVERAGEIFS` it is required and comes first. When omitted, `AVERAGEIF` averages the criteria range itself.

Empty cells and text in the average range are ignored, but a cell that evaluates to 0 is a number and is included in the average.

Basic AVERAGEIF Example

This example demonstrates the simplest use of AVERAGEIF to average sales for a specific region.

A (Region)	B (Sales)	C
East	100
West	200
East	150
		=AVERAGEIF(A1:A3, "East", B1:B3)

Basic AVERAGEIF formula

=AVERAGEIF(A1:A3, "East", B1:B3)

This formula averages sales values in B1:B3 where the corresponding region in A1:A3 is "East". The result is 125 ((100+150)/2). The third argument specifies which values to average when the condition is met.

AVERAGEIF with Comparison Operator

AVERAGEIF supports comparison operators such as >, <, >=, and <=. This example averages sales above a certain threshold.

A (Sales)	B
500
300
700
	=AVERAGEIF(A1:A3, ">400")

AVERAGEIF with operator

=AVERAGEIF(A1:A3, ">400")

This formula averages values in A1:A3 that are greater than 400. The result is 600 ((500+700)/2). Note we omitted the third argument since we're averaging the same range we're checking for criteria. The operator and the value are written together inside the quotes.

To compare against a cell instead of a literal, join the operator to the reference with the & character: =AVERAGEIF(A1:A3, ">"&D1). If D1 contains 400, this produces the same result as the formula above.

AVERAGEIF with Wildcards

AVERAGEIF supports wildcards (* for multiple characters, ? for single character) when working with text criteria. This example averages sales for product names starting with "Pro".

A (Product)	B (Sales)	C
Pro100	50
Basic200	30
Pro200	70
		=AVERAGEIF(A1:A3, "Pro*", B1:B3)

AVERAGEIF with wildcard

=AVERAGEIF(A1:A3, "Pro*", B1:B3)

This formula averages sales in B1:B3 where the product name in A1:A3 starts with "Pro". The result is 60 ((50+70)/2). The asterisk matches any characters after "Pro", and the question mark would match exactly one character. To match an actual asterisk or question mark, precede it with a tilde (~).

AVERAGEIF with Dates

Dates in Excel are stored as serial numbers, so they can be compared like any other number. This example averages sales that occurred after a given date.

A (Date)	B (Sales)
1/1/2023	100
2/1/2023	200
3/1/2023	300
	=AVERAGEIF(A1:A3, ">"&DATE(2023,1,15), B1:B3)

AVERAGEIF with date criteria

=AVERAGEIF(A1:A3, ">"&DATE(2023,1,15), B1:B3)

This formula averages sales dated after January 15, 2023. The result is 250 ((200+300)/2), since the first row falls on the boundary and is excluded. The DATE function returns a real date serial, which is safer than typing a date inside the criteria string because it is not affected by the regional date format.

Basic AVERAGEIFS Example

AVERAGEIFS extends AVERAGEIF by supporting multiple criteria. This example averages sales for a specific region and product.

A (Region)	B (Product)	C (Sales)	D
East	Widget	100
West	Gadget	200
East	Widget	150
			=AVERAGEIFS(C1:C3, A1:A3, "East", B1:B3, "Widget")

Basic AVERAGEIFS formula

=AVERAGEIFS(C1:C3, A1:A3, "East", B1:B3, "Widget")

This formula averages sales in C1:C3 where region is "East" AND product is "Widget". The result is 125 ((100+150)/2). Note the different argument order compared to AVERAGEIF: the range to average comes first, followed by each criteria range and its criteria.

AVERAGEIFS with Multiple Criteria Types

This advanced example combines different criteria types (text, number, date) in AVERAGEIFS to calculate average sales meeting complex conditions.

A (Region)	B (Sales)	C (Date)	D
East	500	1/1/2023
West	300	2/1/2023
East	700	3/1/2023
			=AVERAGEIFS(B1:B3, A1:A3, "East", B1:B3, ">400", C1:C3, ">"&DATE(2023,1,15))

Complex AVERAGEIFS formula

=AVERAGEIFS(B1:B3, A1:A3, "East", B1:B3, ">400", C1:C3, ">"&DATE(2023,1,15))

This formula averages sales where: region is "East", sales > 400, and date is after Jan 15, 2023. The result is 700 (only the third row meets all criteria). Note the DATE function and concatenation with & for the date comparison.

AVERAGEIFS with Wildcards

Wildcards work in AVERAGEIFS as well, and they can be combined with other conditions. This example averages East region sales for products whose name starts with "Pro".

A (Product)	B (Region)	C (Sales)
Pro100	East	50
Basic200	East	30
Pro200	West	70
		=AVERAGEIFS(C1:C3, A1:A3, "Pro*", B1:B3, "East")

AVERAGEIFS with wildcard

=AVERAGEIFS(C1:C3, A1:A3, "Pro*", B1:B3, "East")

Only the first row satisfies both conditions (the product starts with "Pro" and the region is "East"), so the result is 50. The second "Pro" row is excluded because its region is "West".

How the Average Range Is Sized

The two functions differ in how strictly they require the average range to match the criteria range.

For AVERAGEIF, if the average range is a different size, Excel uses its top-left cell as the anchor and resizes it to match the shape of the criteria range. In the table below the formula is written with B1:B2, yet Excel treats it as B1:B3, so the result is 125.

A (Region)	B (Sales)
East	100
West	200
East	150
	=AVERAGEIF(A1:A3, "East", B1:B2)

AVERAGEIF resizes the average range

=AVERAGEIF(A1:A3, "East", B1:B2)

For AVERAGEIFS this shortcut does not apply. The average range and every criteria range must have exactly the same number of rows and columns, otherwise the formula returns #VALUE!.

AVERAGEIFS requires matching sizes

=AVERAGEIFS(B1:B2, A1:A3, "East")

Because B1:B2 and A1:A3 have different shapes, this formula returns #VALUE! instead of an average. Always build the criteria ranges and the average range over the same rows.

Empty Cells, Text, and Zeros

Within the average range, Excel ignores empty cells and text, but it does include any cell that evaluates to the number zero. The example below averages the numeric values in column B for the "X" rows.

A	B
X	10
X
X	text
X	0
Y	100
	=AVERAGEIF(A1:A5, "X", B1:B5)

Empty, text, and zero values

=AVERAGEIF(A1:A5, "X", B1:B5)

The empty cell and the text cell are skipped, while the zero counts, so the result is 5 ((10+0)/2). This is different from counting: the zero is a real value, not a missing one. If every matching cell is empty or text, the function has nothing to average and returns #DIV/0!.

AVERAGEIF/AVERAGEIFS Errors and Troubleshooting

Cause	Symptom	Fix
No cell meets the criteria	#DIV/0! error	Check the criteria spelling and logic, or wrap the call in `IFERROR` to show a blank or 0
A criteria range and the average range have different sizes in `AVERAGEIFS`	#VALUE! error	Make every criteria range and the average range the same size and shape
The operator and value are split into separate arguments	#VALUE! error or no match	Write the operator and value together in one quoted string, for example `">400"`
A cell reference is not joined to its operator	Wrong result or no match	Concatenate with `&`, for example `">"&D1`
The average range holds only text or blanks	#DIV/0! error	Point the average range at numeric cells; text and blanks are ignored
Dates are compared as text	Wrong or empty result	Build the criteria with a date serial, for example `">"&DATE(2023,1,15)`
An `AVERAGEIF` average range has the wrong shape	Unexpected average	Remember it is resized from its top-left cell to match the criteria range

The AVERAGEIF and AVERAGEIFS functions make it easy to average a subset of data without sorting or filtering it first. Remember the reversed argument order: the average range is last and optional in AVERAGEIF, but first and required in AVERAGEIFS. Quote comparison criteria, join operators to cell references with &, and build date criteria with DATE. Keep the average range and every criteria range the same size, and guard against #DIV/0! when a condition might match nothing.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all Excel Formulas.

Ebooks