How to Use AVERAGEIFS in Excel

AVERAGEIFS returns the average of values in a range that meet one or more criteria. Like SUMIFS, the value range comes first, followed by pairs of criteria range and criteria. In models it produces conditional averages such as average sale by segment or average days to pay by vendor, without filtering or building a pivot table.

Syntax and argument order

The signature is =AVERAGEIFS(average_range, criteria_range1, criteria1, ...). The range you average comes first, then each criteria range pairs with its criteria. This matches SUMIFS and is the reverse of the older AVERAGEIF, where the average range comes last.

Each criteria range must be the same dimensions as the average range. Multiple criteria pairs combine with AND logic, so a row is included in the average only when it satisfies every condition. Criteria can be text, numbers, or comparison strings like ">=10".

Worked example: average sale by region and quarter

Amounts sit in D2:D5, regions in B2:B5, and quarters in C2:C5. You want the average West region sale in Q2.

1. Enter =AVERAGEIFS(D2:D5, B2:B5, "West", C2:C5, "Q2").
2. Excel keeps rows where region is West and quarter is Q2.
3. Two rows qualify, with amounts 12000 and 8000.
4. AVERAGEIFS divides their total of 20000 by 2 and returns 10000.

Two rows match West and Q2. Their average, (12000 plus 8000) divided by 2, is 10000.

How analysts use AVERAGEIFS in models

AVERAGEIFS turns transaction level detail into clean conditional benchmarks.

• Compute average ticket size by product line for pricing analysis.
• Find average days sales outstanding for a single customer over a period.
• Bound a date range with two criteria pairs to average within a window.
• Reference the criteria from input cells so the benchmark updates with your filters.

The DIV/0 error and other pitfalls

If no rows meet all the criteria, AVERAGEIFS returns a #DIV/0! error because it tries to divide a sum of zero matching values by a count of zero. Wrap the formula to handle this, for example =IFERROR(AVERAGEIFS(D2:D5, B2:B5, "West", C2:C5, "Q2"), 0) or use an IF on COUNTIFS to test for matches first.

Two other traps are common. Blank cells in the average range are skipped, but cells holding text or zero behave differently, since a true zero is included in the average while a blank is not. And as with all the IFS functions, argument order is the top mistake, so always put the average range first.