Average by Group

Excel Formulas › Average

All versionsAVERAGEIFAVERAGEIFS

To average the numbers that belong to one category — the mean order value for the West region, the average score per class — use AVERAGEIF. It averages only the rows that match your condition, ignoring the rest.

Quick formula: to average column C for rows where region (B) equals E2:
=AVERAGEIF(B2:B8, E2, C2:C8)
The first range is what to test, the criteria is the group, and the third range is what to average.

Functions used (tap for the full reference guide):

AVERAGEIFAVERAGEIFS

The example

Orders by region. We want the average West order.

ABCEF
1RepRegionAmountRegionAvg
2AnaWest$120West$150
3BenEast$90
4CyWest$180
5DotEast$70
6EveWest$150

The formula

The average West order:

=AVERAGEIF(B2:B6, "West", C2:C6) // (120+180+150)/3 = 150

How it works

AVERAGEIF averages only the matching rows:

  1. The first range, B2:B6, is tested against the criteria "West".
  2. For every row that matches, AVERAGEIF takes the value from the third range, C2:C6.
  3. It sums those matching values and divides by how many there were — (120+180+150)/3 = 150.
  4. Non-West rows are ignored entirely; they don’t affect the count or the total.

Two or more conditions? Use AVERAGEIFS — note the average range comes first: =AVERAGEIFS(C2:C6, B2:B6, "West", D2:D6, "Widget").

Try it: interactive demo

Live demo

Pick a region; see its average order value.

Average:

Variations

Average with two conditions

AVERAGEIFS — average range first:

=AVERAGEIFS(C2:C6, B2:B6, "West", A2:A6, "A*")

Average greater than a value

Criteria can be a comparison:

=AVERAGEIF(C2:C6, ">100")

Average ignoring zeros

Exclude zero values from the mean:

=AVERAGEIF(C2:C6, "<>0")

Pitfalls & errors

#DIV/0! No rows matched the criteria, so there was nothing to average. Check spelling and that the group actually exists.

AVERAGEIF skips text and blanks automatically in the average range — but a row counts only if its criteria cell matches. Empty value cells in matching rows are ignored, not treated as 0.

AVERAGEIFS reverses the argument order. In AVERAGEIFS the average range is first; in AVERAGEIF it’s last. Mixing them up returns wrong results.

Practice workbook

📊
Download the free Average by Group practice workbook
The orders table with live AVERAGEIF, the AVERAGEIFS and comparison versions, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I average values by category in Excel?
Use =AVERAGEIF(group_range, "Category", value_range), e.g. =AVERAGEIF(B2:B8, "West", C2:C8) averages only the West rows. For multiple conditions use AVERAGEIFS with the average range first.
Why does AVERAGEIF return #DIV/0!?
No rows matched the criteria, so there were no values to average. Verify the group name is spelled correctly and exists in the range.
How do I average ignoring zeros or blanks?
Use a comparison criterion: =AVERAGEIF(range, "<>0") ignores zeros. AVERAGEIF already ignores blank and text cells in the average range.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Weighted average · Sum by month · Max by criteria

Function references: AVERAGEIF · AVERAGEIFS