How to How to Use AVERAGEIFS Function in Excel in Excel - Practical Guide

Learn to use AVERAGEIFS to calculate conditional averages based on multiple criteria. This powerful function filters data by specified conditions and returns the average of matching values, essential for analyzing subsets of data like sales performance by region and quarter.

Why This Matters

AVERAGEIFS enables sophisticated data analysis without manual filtering, saving time and reducing errors when working with large datasets across multiple business dimensions.

Prerequisites

•Basic understanding of Excel formulas and cell references
•Familiarity with AVERAGE and IF functions
•Data organized in columns with headers

Step-by-Step Instructions

Prepare your data

Organize data in columns with headers (e.g., Region, Quarter, Sales). Ensure criteria columns are clearly labeled and data is consistent.

Click on the destination cell

Select the cell where you want the result to appear, typically below or beside your dataset.

Enter the AVERAGEIFS formula

Type the formula: =AVERAGEIFS(average_range, criteria_range1, criterion1, criteria_range2, criterion2). For example: =AVERAGEIFS(D2:D100, A2:A100, "North", B2:B100, "Q1").

Define your ranges and criteria

Specify which column to average (average_range), which columns contain your criteria (criteria_range), and what values to match (criterion).

Press Enter and verify results

Press Enter to execute the formula and check the calculated average against your expected values.

Alternative Methods

SUMIFS with COUNTIFS

Divide SUMIFS by COUNTIFS to calculate conditional averages: =SUMIFS(amount_range, criteria_range, criterion) / COUNTIFS(criteria_range, criterion). Less elegant but provides the same result.

Array formulas with AVERAGE and IF

Use =AVERAGE(IF((condition1)*(condition2), values)) as an array formula (Ctrl+Shift+Enter). More complex but offers flexibility for custom logic.

Tips & Tricks

✓Use absolute references ($A$2:$A$100) when copying formulas to prevent range shifts.
✓Ensure criteria values match exactly (case-insensitive for text) or use wildcards like "North*" for partial matches.
✓Add multiple criteria pairs for more precise filtering without needing helper columns.

Pro Tips

★Combine AVERAGEIFS with named ranges for cleaner, self-documenting formulas that are easier to audit.
★Use cell references instead of hardcoded values for criteria to create dynamic dashboards that update automatically.
★AVERAGEIFS ignores text values in the average_range, making it safe for mixed data types.

Troubleshooting

Formula returns #DIV/0! error

This occurs when no values match your criteria. Verify criteria spelling and data values match exactly, or wrap the formula in IFERROR: =IFERROR(AVERAGEIFS(...), "No data").

Formula returns #VALUE! error

Check that all range arguments have the same number of rows and columns, and that criteria are properly formatted (text in quotes).

Results seem incorrect

Use a helper column with COUNTIFS to verify how many rows match your criteria; confirm the average_range contains numeric values, not text.

Related Excel Formulas

AVERAGE AVERAGEIF SUMIFS COUNTIFS IF

Frequently Asked Questions

Can AVERAGEIFS handle multiple criteria on the same column?

AVERAGEIFS applies AND logic only. To average values where a column is either "North" or "South," use SUMPRODUCT((A2:A100="North")+(A2:A100="South")) / SUMPRODUCT((A2:A100="North")+(A2:A100="South")) or create separate formulas.

How do I use AVERAGEIFS with date criteria?

Dates work as criteria if formatted correctly. Use DATE function or date serial numbers: =AVERAGEIFS(D2:D100, A2:A100, ">="&DATE(2024,1,1), A2:A100, "<="&DATE(2024,12,31)).

Is AVERAGEIFS available in Excel 2010?

Yes, AVERAGEIFS was introduced in Excel 2007. All modern versions including 2010, 2013, 2016, 2019, and Excel 365 support it.