Find the average of a row or column when given condition in same or another corresponding row or column matches. To find average based on a condition in Google Sheets, there is no need to combine IF and Average functions. You can straight away use the AVERAGEIF function in Google Sheets.

How to Use Averageif Function In Google Sheets

Syntax and Explanation:

AVERAGEIF(criteria_range, criterion, [average_range])

criteria_range – The range to check against a condition/criterion.

criterion – The pattern/condition/test to apply to criteria_range.

Usage:

=AVERAGEIF(A2:A9,"Male",B2:B9)

This formula will return the average age of the gender “Male”. In this example, you can say that the gender is in Column A (A2: A9) and it’s called the criteria_range. The text “Male” in the formula is the criterion.

Below you can learn how to use the number, date, comparison operators as criteria in Google Sheets Averageif function.

Find Average Based on Condition in Google Sheets

Here are a few examples.

Number as the criterion in Google Sheets Averageif Function.

I have used the below Averageif formula to find the average marks scored by the students from Class 5.

After seeing this formula, I know some of you may think that there is a typo in this formula.

As I’ve told you, a number is our criterion here. So normally when we use a number as the criterion, it should not be enclosed in double quotes! But I used double quotes here. Why?

I’ll explain it. But here also you can use the formula as below and it’s the proper use.

=AVERAGEIF(B2:B11,5,C2:C11)

See there are no double quotes with criterion in this example. But even if you put double quotes, Google Sheets AVERAGEIF would treat it as a number!

For example, you can put the criterion as 5 or “5” and both are numbers. When you want to use the comparison operators greater than or less than with number criterion, then you should use the number within double quotes as “>5”. I just want to make you understand this with the above usage.

Date criterion in Google Sheets Averageif Function

Honestly, the date criteria always put me in a mess. That’s why I’m giving extra attention to date criteria in my tutorials. Here are a few examples.

1. Usage of Date Criteria in Google Sheets Filter function.

2. How to Use Date as Criteria in Query in Google Sheets.

3. Usage of Date Criteria in Google Sheets Sumproduct Function.

Note: There are more such tutorials on this site that you can search and find.

Now let me explain how to use Date Criteria in Averageif in Google Sheets.

Here I just want to find the average quantity sold on today’s date. So I’ve used the Date Function TODAY as the criterion in Averageif formula.

If you want to directly use the date, it will be as follows.

=AVERAGEIF(A2:A11,DATE(2018,3,19),B2:B11)

Find The Average, If the Cells Are Not Blank

Finding the average of nonblank cells are a little tricky. You can use the comparison operator <> here which is equal to the logical NOT. Here is that use.

I think the above examples are enough to learn how to find average based on the condition in Google Sheets. That’s all. Thanks for the stay!