Averaging by Criteria With AVERAGEIF

You have totaled with SUMIF and counted with COUNTIF. The third member of the family is AVERAGEIF, which finds the average of only the values that meet a condition.

Instead of averaging every sale, you can average just the East region sales, or just the scores above 50. It mixes a filter and a mean into one clean step.

AVERAGEIF uses the same three-part shape as SUMIF:

• range the cells to test
• criteria the condition
• average_range the cells to average

It looks through range, finds matches, then averages the corresponding cells in average_range.

With regions in A2:A10 and amounts in B2:B10, the average sale for the East region tests column A for East and averages the matching values in column B.

Behind the scenes it is doing a SUMIF divided by a COUNTIF, but AVERAGEIF wraps that into a single readable formula.

If the column you test is also the column you want to average, you can leave off the third argument. To average only the scores in B2:B10 that are above 70:

Here column B is both tested and averaged. When you omit average_range, the spreadsheet uses range for both.

As with the other IF functions, store the condition in a cell for flexibility. If D1 holds a region name, point the criteria at D1.

Now one cell drives the average. Changing D1 from East to West recalculates instantly, which is ideal for a summary table or a dashboard selector.

AVERAGEIF accepts operators inside quotes just like its siblings. To average only sales of at least 100:

• ">=100" 100 or more
• "<50" below 50
• "<>0" excluding zeros

That last one is a favorite, because it averages while ignoring zero entries that would otherwise drag the mean down.

To use a threshold from a cell, glue the operator to it with an ampersand. If D1 holds a cutoff, average everything above it.

The & builds the condition text from ">" and the value in D1. This is the same join trick you used with SUMIF and COUNTIF.

AVERAGEIF has one trap the others do not. If no rows match, there is nothing to divide by and you get a #DIV/0! error.

For example, averaging a region that does not exist in the data returns this error. It is the spreadsheet telling you the filter matched zero cells, not that your formula is broken.

Wrap the formula in IFERROR to show a friendly message instead of #DIV/0! when nothing matches.

Now an empty region shows the text No data rather than an alarming error. This keeps reports looking polished even when some categories are missing.

AVERAGEIF only averages cells that contain numbers among the matches. Truly blank cells in the average_range are ignored, so they do not count as zero.

This matters: a missing value will not pull the average toward zero the way a literal 0 would. If you want zeros excluded too, add the criteria "<>0" on the value column.

Build a tidy summary: list each category in cells D2, D3, D4, then write one AVERAGEIF referencing the label cell and fill it down.

Each row now shows the average value for its own category, side by side with the SUMIF total and COUNTIF count from earlier lessons. Together they form a compact, fully formula-driven report.

Test your understanding of AVERAGEIF behavior.

You can now average conditionally with AVERAGEIF. Key points:

• The order is range, criteria, average_range; omit the last argument to average the test range itself.
• It supports text, numbers, and operators like ">=100".
• No matches produces a #DIV/0! error, so guard it with IFERROR.
• Blank cells are skipped, not treated as zero.

Next you will match partial text using wildcards.