AVERAGEIF

Find the average of cells in a range that meet a single condition.

Statistical
|
Excel 2007+
|
Google Sheets Supported

Spreadsheet editor

Syntax

=AVERAGEIF(range, criteria, [average_range]) Returns: Number

Arguments

Argument Required Description
range Yes The cells you want to test against the criteria.
criteria Yes The condition a cell must meet, such as a number, text, a comparison like ">50", or a wildcard pattern.
average_range No The cells to average. If you leave this out, Excel averages the cells in range instead.

About

AVERAGEIF returns the average of the numbers in a range, but only for the cells that match a condition you set. You give it a range to check, the criteria a cell has to meet, and optionally a separate range of numbers to average. If you leave out that third part, Excel averages the same cells it checks.

Use AVERAGEIF when a plain AVERAGE includes too much. It is the go-to for questions like the average sale for one region, the average score above a passing grade, or the average order value for a single customer. The criteria can be a number, text, a comparison like ">100", or a wildcard pattern, so it handles both text and numeric filters.

When you need more than one condition, reach for AVERAGEIFS instead. For conditional totals and counts, the matching tools are SUMIF and COUNTIF, which use the same criteria style.

Examples

Average only the passing scores

See what happens when you skip the third argument. AVERAGEIF tests and averages the same column, so you instantly get the mean of every score above 70. Edit a score and watch the average shift.

Spreadsheet editor

Avoid the #DIV/0! error

Watch what happens when no row matches your criteria. On its own AVERAGEIF returns #DIV/0!, so wrap it in IFERROR to show a clean 'No data' message instead. Change 'Cherries' to a real product to see the average appear.

Spreadsheet editor

Pull the threshold from a cell

Build your criteria from another cell by joining the operator with ">"&B7. Type a new minimum in cell B7 and watch the average recalculate for every amount above it.

Spreadsheet editor

Watch out for

⚠

#DIV/0! when nothing matches

If no cells meet the criteria, or the range only holds text and blanks, AVERAGEIF returns #DIV/0! because it has no numbers to divide.

→ Double-check that your criteria matches the data exactly, then wrap the formula in IFERROR to show a friendly message like "No data" instead of the error.

⚠

Forgetting quotes around comparisons

Writing the criteria as >100 without quotes causes an error. Comparisons and text must be inside double quotes.

→ Always quote text and operator criteria, like ">100" or "West". To combine an operator with a cell reference, use ">"&E1.

⚠

Mismatched range sizes

When range and average_range cover different numbers of cells, Excel starts at the top-left cell of average_range and reads cells that match the shape of range, which can average the wrong rows.

→ Keep range and average_range the same size and aligned to the same rows so each tested cell lines up with the value you want to average.

Tips & notes

AVERAGEIF ignores empty cells in the average range and skips cells containing TRUE or FALSE. Criteria text is not case-sensitive, so "west" and "West" match the same cells.

Common questions

Can AVERAGEIF use more than one condition?

No, AVERAGEIF only handles a single condition. For multiple criteria, use AVERAGEIFS, which lets you stack as many conditions as you need.

Why does AVERAGEIF return #DIV/0!?

It means no cells matched your criteria, so there were no numbers to average. Confirm the criteria matches your data exactly, including spelling and spacing.

Does the criteria work with text?

Yes. You can match exact text like "Acme Corp" or use wildcards such as "North*". Text matching is not case-sensitive.