AVERAGEIF Function

Summary

AVERAGEIF returns the average of values that meet one condition. It is useful when you do not want the mean for the whole dataset and only care about one group, one threshold, or one matching label.

That makes it a practical reporting function. Instead of filtering a list by hand and then averaging it, you can let one formula do both jobs at once. You tell Excel what to check, what rule to use, and which numbers should be averaged.

AVERAGEIF is useful when the worksheet needs the average for one filtered subset instead of the average of the whole range. That makes it practical in reports where one category, label, or threshold should define which values belong in the calculation.

Syntax

=AVERAGEIF(range, criteria, [average_range])

range is the part Excel checks against your rule. criteria is the rule itself. average_range is the set of values to average if it is different from the checked range.

Arguments

• range - [required] The cells Excel tests against the condition.
• criteria - [required] The rule that decides which cells count. This can be text, a number, an expression like ">1000", or a cell reference.
• average_range - [optional] The actual cells to average. If you leave it out, Excel averages the cells in range.

AVERAGEIF vs Other Functions

The difference mostly comes down to how many conditions you need and whether you want an average at all.

Using the AVERAGEIF Function

A common pattern is averaging one numeric column based on labels in another column. For example, you can average salaries where the department is "Sales", or average order values where the region contains the word "North". This keeps the formula readable even when the dataset grows.

Microsoft also notes a few details that help avoid mistakes. Criteria can be written as text, numbers, expressions, or cell references. That means rules like "Sales", 32, ">32", and B4 are all valid. If you omit average_range, Excel averages the matching cells from range itself.

Wildcards are useful when the text is not identical in every row. The asterisk * matches any sequence of characters, and the question mark ? matches a single character. That lets one formula catch values like North East and North West without listing each one separately.

Example 1 - Average salaries for Sales

In this example, the department names are in one column and the salaries are in another. The formula first checks which rows belong to Sales, then it ignores every row that belongs to some other department.

After the matching rows are found, Excel averages only the salary values from those rows. That makes the result useful when you want the typical salary for one team instead of the average across the whole company.

=AVERAGEIF(A1:A3,"Sales",B1:B3) // Averages only the Sales rows.

Example 2 - Average values above 1000

This example uses one numeric list for both steps. Excel checks which values are greater than 1000, and because the checked values are also the values being averaged, there is no need for a separate average_range.

The result answers a more focused question than a normal average. Instead of asking for the average of all values, it asks for the average of only the larger values that pass the threshold.

=AVERAGEIF(B1:B10,">1000") // Averages only values greater than 1000.

Example 3 - Average rows that contain North

Here the text labels are not all exactly the same. Some rows may say North East and others may say North West, so an exact match would miss part of the group you really want.

The wildcard lets Excel include any row where the text contains North, then average the numbers from those matching rows. This is helpful when categories share a pattern but are not written as one identical label.

=AVERAGEIF(A1:A10,"*North*",B1:B10) // Averages all matching North rows.

Example 4 - Use a cell as the condition

In this setup, the condition is not typed directly into the formula. Instead, the formula reads the category from another cell, which makes the report easier to update without editing the formula itself.

That means the same formula can answer different questions just by changing the criteria cell. It is a simple pattern, but it is very useful in dashboards and summary tables.

=AVERAGEIF(A1:A10,C1,B1:B10) // Uses the rule stored in C1.

Conclusion Recap

AVERAGEIF is useful when you want the average for one part of your data, not the whole list. In this lesson, that meant averaging one category, values above a limit, text that contains a word, or a condition taken from a cell.

The idea stays simple in every case. Excel checks one rule, keeps the matching rows, and averages only those values.

• Main job: AVERAGEIF returns the mean for values that match one condition.
• Flexible criteria: You can use text, numbers, operators, wildcards, or cell references.
• Optional average range: If you leave it out, Excel averages the matching cells in range.
• Wildcard support: * matches many characters and ? matches one.
• No matches: If nothing meets the condition, the result is #DIV/0!.
• Next step: Use AVERAGEIFS when one condition is not enough.