How to Use AVERAGEIF and AVERAGEIFS in Excel

Introduction

AVERAGEIF and AVERAGEIFS calculate the average of cells that meet one or more conditions. If you already use COUNTIF to count and SUMIF to sum based on criteria, these two complete the set by giving you conditional averages without extra columns or manual filtering.

The Dataset

Here's an employee performance table we'll use throughout:

	A	B	C	D
3	Employee	Department	Region	Score
4	Sarah	Sales	East	88
5	James	Engineering	West	74
6	Maria	Sales	West	92
7	David	Engineering	East	81
8	Priya	Sales	East	95
9	Marcus	Engineering	West	68

We want to find average scores by department, by region, and by both at the same time.

AVERAGEIF

AVERAGEIF calculates the average of cells in a range that meet a single condition. The syntax is:

=AVERAGEIF(range, criteria, [average_range])

• range: The range of cells to evaluate against the criteria.
• criteria: The condition that determines which cells are included.
• average_range: The cells to average. If omitted, Excel averages the cells in range.

Average by Department

To find the average score for Sales employees:

=AVERAGEIF(B4:B9, "Sales", D4:D9)

AVERAGEIF checks column B for "Sales" and averages the corresponding values in column D.

Employee	Department	Score	Included?
Sarah	Sales	88	Yes
James	Engineering	74	No
Maria	Sales	92	Yes
David	Engineering	81	No
Priya	Sales	95	Yes
Marcus	Engineering	68	No

The result is (88 + 92 + 95) / 3 = 92 (rounded from 91.67, though Excel returns the precise value).

For Engineering:

=AVERAGEIF(B4:B9, "Engineering", D4:D9)

The result is (74 + 81 + 68) / 3 = 74 (rounded from 74.33).

Department	Formula	Average Score
Sales	=AVERAGEIF(B4:B9, "Sales", D4:D9)	91.67
Engineering	=AVERAGEIF(B4:B9, "Engineering", D4:D9)	74.33

Using Comparison Operators

Like COUNTIF and SUMIF, AVERAGEIF supports comparison operators. To find the average score among employees who scored above 80:

=AVERAGEIF(D4:D9, ">80")

When the criteria apply to the same range you want to average, you can omit the third argument. Excel evaluates D4:D9 for values greater than 80 and averages those same cells.

Employee	Score	Above 80?
Sarah	88	Yes
James	74	No
Maria	92	Yes
David	81	Yes
Priya	95	Yes
Marcus	68	No

The result is (88 + 92 + 81 + 95) / 4 = 89.

AVERAGEIFS

AVERAGEIFS works like AVERAGEIF but accepts multiple conditions. Every condition must be true for a row to be included. The syntax is:

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

• average_range: The cells to average (this comes first, unlike AVERAGEIF).
• criteria_range1: The first range to evaluate.
• criteria1: The condition for the first range.
• criteria_range2, criteria2: Additional range-condition pairs.

Notice that the argument order is different from AVERAGEIF. In AVERAGEIFS, the average_range comes first. In AVERAGEIF, it comes last.

Average by Department and Region

To find the average score for Sales employees in the East region:

=AVERAGEIFS(D4:D9, B4:B9, "Sales", C4:C9, "East")

Employee	Department	Region	Score	Both Match?
Sarah	Sales	East	88	Yes
James	Engineering	West	74	No
Maria	Sales	West	92	No
David	Engineering	East	81	No
Priya	Sales	East	95	Yes
Marcus	Engineering	West	68	No

Only Sarah (88) and Priya (95) are in Sales and in the East. The result is (88 + 95) / 2 = 91.5.

Here's a full breakdown by department and region:

Department	Region	Formula	Average
Sales	East	=AVERAGEIFS(D4:D9, B4:B9, "Sales", C4:C9, "East")	91.5
Sales	West	=AVERAGEIFS(D4:D9, B4:B9, "Sales", C4:C9, "West")	92
Engineering	East	=AVERAGEIFS(D4:D9, B4:B9, "Engineering", C4:C9, "East")	81
Engineering	West	=AVERAGEIFS(D4:D9, B4:B9, "Engineering", C4:C9, "West")	71

Combining Comparison Operators

AVERAGEIFS can mix text criteria with numeric thresholds. To find the average score for Sales employees who scored above 90:

=AVERAGEIFS(D4:D9, B4:B9, "Sales", D4:D9, ">90")

Employee	Department	Score	Sales AND >90?
Sarah	Sales	88	No
James	Engineering	74	No
Maria	Sales	92	Yes
David	Engineering	81	No
Priya	Sales	95	Yes
Marcus	Engineering	68	No

The result is (92 + 95) / 2 = 93.5.

Notice that the average_range (D4:D9) appears twice: once as the range to average and once as a criteria range. This is valid and common when you need to filter the values you're averaging.

AVERAGEIF vs AVERAGEIFS

	AVERAGEIF	AVERAGEIFS
Conditions	One	Multiple
Argument order	range, criteria, average_range	average_range, criteria_range, criteria
average_range position	Last (optional)	First (required)
Use when	Filtering by a single column	Filtering by two or more columns

The swapped argument order is the most common source of errors when switching between the two. If your AVERAGEIFS formula returns unexpected results, check that the average_range is the first argument, not the last.

Key takeaway: Use AVERAGEIF for single-condition averages. Switch to AVERAGEIFS when you need two or more conditions, and remember that the argument order changes.

Conclusion

AVERAGEIF and AVERAGEIFS give you conditional averages the same way COUNTIF and SUMIF give you conditional counts and sums. They keep your analysis in formulas instead of requiring filtered views or helper columns.

For conditional counting and summing, check out our guide on COUNTIF and SUMIF. If you need multi-criteria counting, see our guide on COUNTIFS, or for multi-criteria sums, see SUMIFS.