Averageifs Multiple Criteria Function in Google Sheets

Learn all about the AVERAGEIFS function in Google Sheets here. I am detailing every aspect of Averageifs multiple criteria function in Google Sheets that may come to you very useful in real-life use.

Averageifs is a Statistical function but with a logical flavor. It’s a statistical function as you can use it to find the average of a range. But at the same time, you can apply logical conditions in calculating the average.

The Syntax of Averageifs Multiple Criteria Function:

AVERAGEIFS(average_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])

Here the function argument average_range is the range to find an average. Criterion1 is the first criteria, condition or pattern to test in the criteria_range1. If you want to use only a single criterion you can use the Averageif function.

Here you can use multiple criteria and of course multiple criteria range. I think I can better convey to you what I want to say with a few examples.

How to Use Averageifs Multiple Criteria Function in Google Sheets

To make you understand the use of different criteria in Averagifs like date, text, numeric, comparison operators, in each example below, I’ve chosen different criteria.

1. Averageifs in Single Criteria Similar to Averageif

When I say Averageifs Multiple Criteria function, it doesn’t mean that you can only use multiple criteria in Averageifs. On the contrary, you can use single criteria too similar to Averageif.

Text criteria in Averagifs in Google Sheets

In the below example formula, I find the average age of females in a table. Unlike Averageif, in Averageifs the average range comes first. That is column C here.

Averageifs in Single Criteria

Then how to use Averageif in the above example. See the formula below.

=averageif(B2:B9,"F",C2:C9)

The difference between Averageif and Averageifs in Google Sheets is in the criteria used. In Averageif, you can only use one single criterion whereas in Average ifs you can use multiple criteria.

Another difference is the positioning of arguments. As you can see clearly above, in Averageif the criteria range comes first, but in Averageifs it’s the Average range.

2. Averageifs with Multiple Condition

This example justifies the use of Averageifs Multiple Criteria functions in Google Sheets. The below example shows you how to use multiple criteria in Averageifs in Google Sheets. Also, I’ve used the comparison operator here.

Averageifs with Multiple Condition

3. Date as Criteria in Averageifs

When you want to use a date as the criterion in Averageifs, simply follow the below example. The date should be used in thedate(yyyy,mm,dd) format.

Date as Criteria in Averageifs

Note: The average range can be number or date.

Can we Use OR criteria in Averageifs?

Certainly not. In some situations, you may want to find average based on multiple criteria in the same column. But you are not permitted to use logical OR in Averageifs. So the solutions are either use an Average + Filter function combo or Query. The versatility of Query functions always amazes me!

Find Average in Google Sheets When the Multiple Conditions Fall in the Same Column

Or condition in Average Ifs in Google Sheets

Here I am finding the average age of both Male and Females. I just want to demonstrate how to use multiple conditions in the same column in Google Sheets.

I’ve just picked a sample data for this example which may or may not make any sense in real-life use.

Formula 1:

=average(filter(A2:C9,(B2:B9="F")+(B2:B9="M")))

Formula 2:

=query(A2:C9,"Select Avg(C) where B='F' or B='M'")

I think the above examples are enough to learn the use of Averageifs Multiple Criteria Function in Google Sheets. Hope you have enjoyed the stay!

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.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

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...

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.