AVERAGEIFS Multiple Criteria Function in Google Sheets

The AVERAGEIFS function in Google Sheets is a statistical function with a logical flavor. It allows you to calculate the average of a range while applying multiple conditions. Unlike the simpler AVERAGEIF, which works with a single condition, AVERAGEIFS is designed for scenarios that require multiple criteria to filter the data.

If you only need to filter a table based on a single condition, the AVERAGEIF function is a simpler choice. However, if you need to apply multiple criteria, AVERAGEIFS is the function to use. That said, alternatives such as DAVERAGE or QUERY, along with combination formulas, can also achieve this purpose.

Syntax

AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
  • average_range: The range of cells containing the values you want to average.
  • criteria_range1: The first range to which the first criterion applies.
  • criterion1: The condition, pattern, or test to apply to criteria_range1.
  • You can add additional criteria by specifying more criteria_range and criterion pairs.

Sample Data

For this example, we’ll use the following sample data in columns A to D:

Sample data used to test the AVERAGEIFS function in Google Sheets.

In this dataset, columns A to D represent player names, birthdates, ages, and their selection status.

Examples of Using the AVERAGEIFS Function

1. Single Criterion Example

The AVERAGEIFS function can be used with a single criterion, similar to AVERAGEIF. For example, to find the average age of players who are marked as “Selected”, use this formula:

=AVERAGEIFS(C2:C, D2:D, "Selected")
  • C2:C contains the ages of players.
  • D2:D contains the selection status (either “Selected” or “Not Selected”).

2. Multiple Criteria Example

To calculate the average age of players who are both “Selected” and older than 25, use this formula:

=AVERAGEIFS(C2:C, D2:D, "Selected", C2:C, ">25")

This will return the average age of players who meet both criteria.

3. Using Comparison Operators in AVERAGEIFS

You can use various comparison operators (such as >, <, >=, <=, =) in your criteria. Here’s an example that calculates the average age of players whose names are not empty and their status is “Selected”:

=AVERAGEIFS(C2:C, A2:A, "<>", D2:D, "Selected")

This formula ensures that it only averages the ages of players whose names are not blank and whose status is “Selected”.

4. Using Dates as Criteria in AVERAGEIFS

You can also use dates as criteria. The DATE function or the ISO 8601 format (yyyy-mm-dd) can be used. Here’s an example to calculate the average age of players born on or after January 1, 1995:

=AVERAGEIFS(C2:C, B2:B, ">="&DATE(1995, 1, 1))

Alternatively, you can specify the date directly:

=AVERAGEIFS(C2:C, B2:B, ">=1995-01-01")

Both formulas will return the average age of players born on or after January 1, 1995.

Can We Use OR Criteria in AVERAGEIFS?

In some situations, you may want to find the average based on multiple criteria in the same column, such as finding players who are either “Selected” or “Not Selected”. Unfortunately, AVERAGEIFS doesn’t directly support the OR condition. For example, the following formula would return a #DIV/0! error:

AVERAGEIFS(C2:C, D2:D, "Selected", D2:D, "Not Selected")

To achieve this behavior, use ARRAYFORMULA combined with the following formula:

=ARRAYFORMULA(AVERAGEIFS(C2:C, (D2:D="Selected")+(D2:D="Not Selected"), 1))

Here’s what happens:

  • (D2:D="Selected") + (D2:D="Not Selected") creates an array of TRUE and FALSE values. When summed, TRUE becomes 1 and FALSE becomes 0.
  • The ARRAYFORMULA function processes this array, allowing the AVERAGEIFS function to handle multiple criteria as OR conditions.

Additional Tips:

You can replace AVERAGEIFS with QUERY or a combination of FILTER and AVERAGE for more complex filtering scenarios. In a structured dataset, you can also use DAVERAGE. Understanding these functions will expand your ability to filter and average data dynamically.

You can explore more about these functions in my Google Sheets Function Guide.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.