Mastering AVERAGEIFS in Google Sheets

What AVERAGEIFS does in Google Sheets

The AVERAGEIFS function calculates the average of numbers in a given range that meet multiple criteria. The syntax is straightforward and supports numeric, text, and date criteria. You can combine criteria ranges of equal length to filter data before averaging, making it ideal for multi-condition data analysis in Sheets.

Parameters:

• average_range: Range with values to average
• criteria_range1...: Ranges to filter by respective criteria
• criterion1...: Conditions like ">0", "West", "Widget A"

Simple two-criteria example

Consider a sales table with columns A: Region, B: Product, C: Revenue. To find the average revenue for West region and Widget A products:

This formula returns the average of C2:C100 where A2:A100 equals West and B2:B100 equals Widget A. You can adapt the ranges to your data and use named ranges for readability.

Notes: ensure all ranges are the same length; otherwise, Google Sheets returns an error.

Date-based filtering

When dates are involved, you can constrain the date range using DATE or serial date numbers. For example, to average revenue in 2025:

This approach keeps your date logic explicit and readable. If your dates are in a separate column, reference that column for the date criteria just like any other range.

Tip: use concatenation with DATE to build dynamic ranges like ">="&DATE(2025,1,1) for clarity.

Text criteria and wildcards

Text criteria often benefit from wildcards. The asterisk (*) matches any number of characters, while the question mark (?) matches a single character. This makes it easy to filter by prefixes, suffixes, or partial matches:

This formula averages amounts in regions that start with North and where the sales channel is Online. Wildcards can be combined with exact text or operators like =, <>, >, <, etc.

Caution: wildcards only apply to text criteria; numeric criteria should use operators and literals.

Handling missing matches with IFERROR

Sometimes no records meet all criteria. In that case, AVERAGEIFS returns #DIV/0!. Wrap the formula with IFERROR to provide a safe fallback:

This returns 0 (or another value you choose) when no data matches. Using IFERROR keeps your dashboards tidy and avoids rare crashes in live reports.

Named ranges and dynamic criteria

Named ranges improve readability and maintainability, especially in complex sheets. You can replace explicit ranges with named ranges:

If your criteria need to adapt over time, combine named ranges with dynamic criteria like TODAY() or a cell reference:

Here A1 could contain a status value you change to filter results on the fly.

Alternative approaches and performance notes

For very large datasets, FILTER + AVERAGE can be faster and more flexible:

This approach can simplify nested logic and is sometimes more transparent for collaborators. If performance becomes an issue, consider reducing the data range or using QUERY to pre-aggregate before averaging.

Real-world dataset walkthrough

Suppose you have a dataset with columns Region (A), Product (B), Date (C), and Revenue (D). To get the average revenue for West region for Widget A products during 2025, you can combine text and date criteria:

If you later add a new product or region, simply extend the ranges or switch to named ranges to keep the formula maintainable. This section shows how AVERAGEIFS scales with more complex real-world conditions.