How to Use AVERAGEIF in Google Sheets [3 Use Case]

In Today’s topic, we are going to learn how to use AVERAGEIF in Google Sheets, we will also discuss its benefits and an overview of the syntax of the AVERAGEIF function. There may be many scenarios where we can use the AVERAGEIF function in Google Sheets according to given conditions. We will also see several different examples of using the AVERAGEIF function to understand the use of AVERAGEIF in Google Sheets.

Benefits of using AVERAGEIF in Google Sheets

Let’s suppose, you have data containing values for several domains and you need to find the average of some of them or any specific of them, you don’t need to filter data or separate your data, you can easily find the average by specifying the criteria in the AVERAGEIF formula in Google Sheets.

Syntax

=AVERAGEIF(criteria_range, criteria, [average_range])

• Criteria_range: This is the range in which the formula searches for the criterion. The formula counts the number of items that meet the specified standard in the range (for the denominator)
• Criteria: This is a condition that an item incorporated in the average needs to meet. This value could be text, date, number, etc.
• Average_range: This is the range containing the values (for the numerator) based on which the formula calculates the average number. You can input a range you include as “criteria_range” in the “average_range”

How to Use AVERAGEIF in Google Sheets

The procedure of the Averageif formula is a little logical and depends on the given data, then we apply the Averageif formula according to the data. In this tutorial, we will see three different examples of using of Averageif formula so you may learn where and how to use Averageif in Google Sheets

USE CASE # 1

Step 1

If you see in the following picture, we have sample data in which three different stores have their sales for three different months.

Step 2

Suppose from the given data, you have to calculate only the average of sales for January of all stores. Then how will you find it from the given pattern of the data?

Step 3

First place your cursor where you want to find the average for January and run the AVERAGEIF formula with an equal sign as I have written in the following picture.

Step 4

After starting the formula, the first argument of the syntax will be the “Criteria_range“. As we are looking for the average of January here, we will give the criteria for the column of months as highlighted below.

Step 5

After giving the criteria range in the syntax we will have to specify the requirements that we are looking for. As we need to know the average of January here, we will write “January” as written below.

Step 6

In the last the third argument in the syntax will be the average range which is “Sales” in the following example so, we have written the sales range in the syntax.

Step 7

The formula for the AVERAGEIF has been completed so just need to press the Enter key to get the result as we have found in the following screenshot.

Step 8

Similarly, in the following data if you need to find average sales for March then what to do?

Step 9

The whole syntax will be the same as above, just we will change the criteria to “March” instead of January as written in the following picture.

Step 10

So, we will get the average sales of March for all the stores from the following data by pressing the Enter key.

USE CASE # 2

Step 1

In this example, if we consider the data there are some students with their total points in different ranges as shown in the following picture.

Step 2

Let’s suppose, we need to find the average of those students who have got above 60 points from the above data what will we do?

Step 3

First, we will run the AVERAGEIF function in the cell where we want to find the average of students having above 60 marks by just writing the average in the cell with an equal sign.

Step 4

As we know the first argument of the AVERAGEIF formula is the criteria range which is the student’s points in the following example because we need to find the average of those students who have above 60 points.

Step 5

The second argument of the AVERAGEIF formula is the criteria that you are looking for, as we are looking for those students who have above 60 points so here, we will give the criteria “<60” as written in the following picture.

Step 6

In this example, we have just two perimeters so we will close the formula by a close bracket and then hit the Enter key to get the result. As you can see in the following screenshot, you have got the average of those students who have points above 60.

USE CASE # 3

Step 1

In the following sample data as well, we have the data of several students with their grades as you can see in the following picture some of them are pass and fail as well.

Step 2

Let’s suppose we need to find the average of only those students who pass, or we can say who do not fail. So, what will we do?

Step 3

Simply first, write the AVERAGEIF function with an equal sign to run it where you want to find the average of those students who do not fail.

Step 4

According to the AVERAGEIF syntax first, we will provide the criteria range from the following data, as we are looking for those students who do not fail so we will give the cell reference of the status column.

Step 5

After giving the criteria range we need to specify the criteria that we are looking for. As we are looking for the average of those students who do not fail, here we have written “<>F” in the syntax.

Where “<>” indicates “Not equal to” in the syntax.

Step 6

Now we will put the reference of the average range from where we are looking for, as we are looking at the average from the student’s point so here, we will give the range of the student’s point.

Step 7

Once you have completed the syntax of the AVERAGEIF formula, close the bracket, and press the Enter key, the result will be in front of you as can be seen in the following picture, we have got the average of those students who are not failing.

Frequently Asked Questions

Can Sparkline in Google Sheets be Used in Combination with AVERAGEIF?

Sparkline in Google Sheets can definitely be used in combination with AVERAGEIF. To learn how to use sparkline in google sheets effectively, you can follow these steps. First, select the range of data you want to apply the sparkline to. Then, go to the Insert menu and choose Chart. Finally, select Sparklines and customize the options to your preference.

What is the difference between the average and AVERAGEIF functions in Google Sheets?

The AVERAGE function only calculates the average in a specific range in Google Sheets. The AVERAGEIF function allows you to compute the average of values in a particular field that meets a specific criterion.

What are the Similarities and Differences Between DSUM and AVERAGEIF Functions in Google Sheets?

The google sheets dsum function and the AVERAGEIF function have both similarities and differences. The DSUM function calculates the sum of values in a specific range, based on specified criteria. On the other hand, the AVERAGEIF function calculates the average of values in a given range, based on certain conditions. Both these functions are powerful tools in Google Sheets for data manipulation and analysis.

Why do we need to use AVERAGEIF in Google Sheets?

When you have such type of data representing different criteria, but you want to find the average of specific values in such cases we use the Averageif function in Google Sheets.

Conclusion

That’s all from how to use AverageIf in Google Sheets. With the help of the above article on how to use averageif in Google Sheets, you can greatly enhance your data analysis capabilities and can quickly and efficiently summarize and analyze large datasets based on specific conditions, to make your sheets more powerful.