Excel AVERAGEIF Function — Average with One Condition (with Examples)

Contents

What does AVERAGEIF do?
Syntax
6 real examples
AVERAGEIFS — multiple conditions
Common mistakes
FAQ

What does AVERAGEIF do?

AVERAGEIF calculates the arithmetic mean of the values in a range where a corresponding range matches a condition you set. It is the conditional version of AVERAGE — just as SUMIF is the conditional version of SUM, and COUNTIF is the conditional version of COUNT.

Use AVERAGEIF when you need the typical value for a specific subset of your data: average sales for one region, average score for one category, average transaction value above a threshold, or average time for one team.

Syntax

=AVERAGEIF(range, criteria, [average_range])

Argument	Description
range required	The column to check the condition against.
criteria required	The condition a row must meet to be included. Text in quotes, numbers and cell references without.
average_range optional	The column with the numbers to average. If omitted, AVERAGEIF averages the range column itself.

6 real examples

Example 1

Average sales for one region

=AVERAGEIF(A2:A100, "North", C2:C100)

Checks column A for "North" and averages the corresponding values in column C.

Example 2

Average values above a threshold

=AVERAGEIF(B2:B100, ">1000")

Averages only values above 1,000. When range and average_range are the same, you can omit average_range.

Example 3

Average excluding zeros

=AVERAGEIF(B2:B100, "<>0")

Zeros often represent missing data rather than genuine zero values. Excluding them gives a more accurate mean.

Example 4

Dynamic criteria from a cell

=AVERAGEIF(A2:A100, E1, C2:C100)

E1 contains the region name. Change E1 and the average updates instantly — the foundation of dynamic dashboards.

Example 5

Wildcard average — products containing a word

=AVERAGEIF(A2:A100, "*coffee*", C2:C100)

The * wildcard matches any text before or after "coffee" — Coffee Latte, Iced Coffee, and Coffee all match.

Example 6

Compare team average to overall average

=IF(AVERAGEIF(A:A,"Team A",B:B) > AVERAGE(B:B), "Above avg","Below avg")

Combines AVERAGEIF with AVERAGE to benchmark a group against the whole.

AVERAGEIFS — multiple conditions

Use AVERAGEIFS when you need to filter by more than one criterion. Important: the average range comes FIRST in AVERAGEIFS, unlike AVERAGEIF where it comes last.

=AVERAGEIFS(avg_range, range1, criteria1, range2, criteria2, ...)

AVERAGEIFS Example

Average Coffee sales in the North region only

=AVERAGEIFS(C2:C100, A2:A100, "Coffee", B2:B100, "North")

Common mistakes

⚠️ Returns #DIV/0! when no rows match

If no rows meet your condition, there is nothing to average — AVERAGEIF returns #DIV/0!. Wrap with IFERROR: =IFERROR(AVERAGEIF(...),"No data").

⚠️ Range and average_range must be same size

A2:A100 and C2:C50 in the same formula will give wrong results. Always use identical row counts for both ranges.

FAQ

Is AVERAGEIF case sensitive?

No. "north", "North" and "NORTH" all match the same rows.

Can AVERAGEIF use dates as criteria?

Yes. =AVERAGEIF(A:A,">"&DATE(2026,1,1),B:B) averages B values where the date in A is after 1 January 2026.

What is the difference between AVERAGEIF and filtering then using AVERAGE?

AVERAGEIF works live in a formula without changing your data view. Filtering is manual and changes what you see. AVERAGEIF is better for dashboards and reports that need to show multiple averages simultaneously.

Can I use wildcards in AVERAGEIF criteria?

Yes. * matches any number of characters, ? matches exactly one character. "North*" matches "North", "Northeast", "Northwest" etc.

Now practise it for real

Practice this formula yourself — type it in a real spreadsheet and get instant feedback. Free, no download needed.

Start the Excel Basics track free →

Related formulas

AVERAGE SUMIF COUNTIF SUMIFS IF MEDIAN

The Excel AVERAGEIF Functionexplained simply