Excel AVERAGEIFS: Summing Averages by Multiple Criteria (A Comprehensive Guide)

Are you tired of manually sifting through your data to calculate averages based on specific conditions? Do you find yourself creating multiple helper columns or using complex nested formulas just to get the average of a subset of your data? If so, you're in the right place! Today, we're diving deep into the powerful and versatile Excel AVERAGEIFS function. This function is a game-changer for anyone who needs to calculate averages based on one or more criteria.

At ExcelFormula Pro, we're all about making your spreadsheet tasks easier. Whether you're using Microsoft Excel, LibreOffice Calc, or Google Sheets, the AVERAGEIFS function is a staple for data analysis. Let's unlock its potential and learn how to sum averages by criteria like a pro!

What is the AVERAGEIFS Function?

The AVERAGEIFS function allows you to calculate the average of a range of cells that meet multiple specified criteria. Unlike the simpler AVERAGEIF function, which only handles a single condition, AVERAGEIFS opens up a world of possibilities for more nuanced data analysis.

Think of it this way: Instead of just asking "What's the average sales for 'Product A'?", you can ask "What's the average sales for 'Product A' in the 'North' region during the 'Q1' period?". This level of detail is invaluable for understanding trends, performance, and making informed decisions.

The Syntax of AVERAGEIFS

Before we jump into examples, let's break down the syntax of the AVERAGEIFS function. Understanding its arguments is key to using it effectively.

The general syntax is as follows:

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

Let's dissect each argument:

• average_range (Required): This is the range of cells that you want to average. This is the actual data you're performing the calculation on.
• criteria_range1 (Required): This is the first range of cells that you want to evaluate against a criterion.
• criteria1 (Required): This is the criterion that defines which cells in criteria_range1 will be averaged. It can be a number, text, a logical expression (like ">10"), or a cell reference.
• [criteria_range2, criteria2], ... (Optional): These are additional ranges and their corresponding criteria. You can include up to 127 range/criteria pairs. The function will only average cells where ALL specified criteria are met simultaneously.

Practical Examples to Illustrate AVERAGEIFS

Theory is great, but seeing AVERAGEIFS in action is where the magic happens. Let's imagine we have a sales dataset like the one below:

Region	Product	Salesperson	Sales Amount	Month
North	Widget	Alice	150	Jan
South	Gadget	Bob	200	Jan
North	Widget	Alice	175	Feb
East	Doodad	Charlie	120	Jan
North	Gadget	Alice	220	Mar
South	Widget	Bob	160	Feb
North	Widget	David	180	Mar
West	Gadget	Eve	250	Jan
North	Doodad	Alice	130	Feb
South	Widget	Bob	190	Mar

Let's assume this data is in cells A1:E11, with headers in row 1.

Example 1: Average Sales for a Specific Product in a Specific Region

We want to find the average sales amount for 'Widget' in the 'North' region.

Formula:

=AVERAGEIFS(D2:D11, B2:B11, "Widget", A2:A11, "North")

Explanation:

• D2:D11: This is our average_range – the sales amounts we want to average.
• B2:B11: This is criteria_range1 – the product names.
• "Widget": This is criteria1 – we're looking for sales of 'Widget'.
• A2:A11: This is criteria_range2 – the region names.
• "North": This is criteria2 – we're looking for sales in the 'North' region.

The formula will return the average of sales amounts where the product is 'Widget' AND the region is 'North'.

Example 2: Average Sales by a Specific Salesperson in a Specific Month

Let's find out the average sales amount generated by 'Alice' in 'Feb'.

Formula:

=AVERAGEIFS(D2:D11, C2:C11, "Alice", E2:E11, "Feb")

Explanation:

• D2:D11: The average_range (Sales Amount).
• C2:C11: criteria_range1 (Salesperson).
• "Alice": criteria1.
• E2:E11: criteria_range2 (Month).
• "Feb": criteria2.

This will calculate the average sales made by Alice specifically in February.

Example 3: Average Sales Amount Greater Than a Certain Value

We can also use logical operators within our criteria. Let's find the average sales amount for 'Gadget' that is greater than 200.

Formula:

=AVERAGEIFS(D2:D11, B2:B11, "Gadget", D2:D11, ">200")

Explanation:

• D2:D11: The average_range (Sales Amount).
• B2:B11: criteria_range1 (Product).
• "Gadget": criteria1.
• D2:D11: criteria_range2 (Sales Amount). Notice we're using the same range for both the average range and a criteria range. This is perfectly valid!
• ">200": criteria2. When using logical operators like >, <, =, or <>, you must enclose them in quotation marks.

This formula finds the average of sales amounts that are for 'Gadget' AND are also greater than 200.

Example 4: Using Cell References for Criteria

Manually typing criteria can be cumbersome, especially if you need to change them frequently. Using cell references makes your formulas dynamic.

Let's say you have a cell (e.g., G2) where you input the Region, and another cell (e.g., H2) where you input the Product. You want to find the average sales for that specified region and product.

Generate Excel Formulas with AI

Need help creating formulas? Use ExcelFormula Pro to generate them instantly with AI!

Try Free