Calculate Conditional Averages with the Excel AVERAGEIFS Function

When creating reports, you may often need to answer questions such as “What are the average sales for the online channel in Seoul?” or “I want to average only orders with sales other than 0 during January.” The Excel AVERAGEIFS function is ideal for these situations.

This article explains how to use AVERAGEIFS to calculate averages for data that meets multiple conditions, from the basics and practical examples to alternatives for older versions and troubleshooting.

Quick Fix – Three Common Patterns in 3 Minutes

Step 1: Data Structure and Basic Pattern

Here is an example sales data structure.

The basic syntax of the AVERAGEIFS function is as follows.

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Step 2: Average with One Condition (Average Sales by Region)

To calculate the average sales amount for the Seoul region, enter the following formula.

=AVERAGEIFS(E2:E100, C2:C100, "Seoul")

Step 3: Average with Multiple Conditions (Product + Region + Date Range)

This example calculates the average sales for orders where the product is Shirt, the region is Seoul, and the order falls within the specified date range.

=AVERAGEIFS($E$2:$E$100,
            $B$2:$B$100, $G$4,
            $C$2:$C$100, "Seoul",
            $A$2:$A$100, ">="&$G$2,
            $A$2:$A$100, "<="&$G$3)

Understanding AVERAGEIFS – Differences from AVERAGE and AVERAGEIF

Comparing AVERAGE, AVERAGEIF, and AVERAGEIFS

AVERAGE calculates the average for an entire range, while AVERAGEIF calculates a conditional average using one condition. The Excel AVERAGEIFS function lets you apply multiple conditions at once, so you can average only values that match criteria such as product, region, channel, and date.

AVERAGEIFS Syntax and Arguments

The formula syntax is as follows.

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• average_range: The numeric range to average
• criteria_range1, criteria1: The first criteria range and criterion
• [criteria_range2, criteria2]: Additional criteria ranges and criteria (multiple pairs allowed)

Supported Versions and Usage Notes

The AVERAGEIFS function is available in most versions of Excel from Excel 2007 onward and in Microsoft 365. The average_range and each criteria_range must have the same dimensions (number of rows and columns); otherwise, errors or incorrect results may occur.

Practical Example 1 – Conditional Average Sales by Product, Region, and Channel

Create Sample Sales Data

Assume you entered the following data in A1:E11.

Calculate an Average with One Condition

This formula calculates the average sales amount for the entire Seoul region.

=AVERAGEIFS(E2:E11, C2:C11, "Seoul")

Calculate an Average with Multiple Conditions

This example calculates the average sales amount for orders where the product is Shirt, the region is Seoul, and the channel is Online.

=AVERAGEIFS(E2:E11,
            B2:B11, "Shirt",
            C2:C11, "Seoul",
            D2:D11, "Online")

Practical Example 2 – Use a Date Range and Target Amount Criteria Together

Average Sales for a Specific Period and Product

Enter the start date in G2, the end date in G3, and the product name in G4, then use the following formula.

=AVERAGEIFS($E$2:$E$11,
            $B$2:$B$11, $G$4,
            $C$2:$C$11, "Seoul",
            $A$2:$A$11, ">="&$G$2,
            $A$2:$A$11, "<="&$G$3)

Average Only Orders at or Above the Target

Enter the target sales amount in J2, then use the formula below to average only orders at or above the target.

=AVERAGEIFS($E$2:$E$11,
            $B$2:$B$11, "Shirt",
            $C$2:$C$11, "Seoul",
            $E$2:$E$11, ">="&$J$2)

Practical Example 3 – Exclude Zeros and Blank Cells, and Remove Outliers

Conditional Average Excluding Zeros

This formula calculates the average for nonzero sales values in the Seoul region only.

=AVERAGEIFS(E2:E100,
            C2:C100, "Seoul",
            E2:E100, "<>0")

Idea for an Average Excluding the Top and Bottom 10% of Outliers

Calculate the upper and lower thresholds in advance, then average only the values between them.

=AVERAGEIFS(E2:E100,
            E2:E100, ">="&$K$3,
            E2:E100, "<="&$K$2)

Alternatives for Versions Without AVERAGEIFS

Use an AVERAGE(IF()) Array Formula

This array formula calculates the average sales for rows where the product is Shirt and the region is Seoul.

=AVERAGE(IF((B2:B11="Shirt")*(C2:C11="Seoul"), E2:E11))

Calculate an Average with SUMIFS / COUNTIFS

This method creates a conditional average directly using SUMIFS and COUNTIFS.

=SUMIFS(E2:E11, B2:B11, "Shirt", C2:C11, "Seoul")
 / 
 COUNTIFS(B2:B11, "Shirt", C2:C11, "Seoul")

Common Mistakes and Troubleshooting

When the Result Is #DIV/0! or a Blank Cell

• No values meet the criteria, or
• the average_range and criteria_range have different dimensions.

When Criteria Do Not Work or Produce Unexpected Values

• Text criteria may contain spaces, or
• dates may have been entered as text.

Troubleshooting Table

Practical Patterns for Combining Other Functions

Combine SUMIFS, COUNTIFS, MAXIFS, and MINIFS

Use SUMIFS for conditional totals, COUNTIFS for conditional counts, AVERAGEIFS for conditional averages, and MAXIFS/MINIFS for conditional maximum and minimum values to easily create summary reports by product, region, and channel.

Expand into a Summary Report Template

After creating a summary table, use row references in the formulas above so you can fill them down to automatically calculate statistics for every combination.

Wrap-Up – Automate Reports with Conditional Averages

Once you understand the Excel AVERAGEIFS function, you can go beyond basic averages and calculate meaningful averages by condition. Try combining it with SUMIFS, COUNTIFS, MAXIFS, and MINIFS in your company’s recurring reports and analysis templates.

Related articles:

• Master Conditional Totals with the Excel SUMIFS Function
• Sum Only Filtered Data with the Excel SUBTOTAL Function
• A Practical Guide to Finding Values with Multiple Criteria in Excel VLOOKUP
• A Complete Guide to Conditional Maximum and Minimum Values with Excel MAXIFS and MINIFS