If you need to find the average of data that meets certain criteria, the AVERAGEIF function in Google Sheets is something that you need to learn.
This useful function will allow you to have more control over the data that gets averaged in your spreadsheet.
In this tutorial, I will show you how to use the AVERAGEIF function in Google Sheets.
Table of Contents
AVERAGEIF Syntax
The AVERAGEIF function will return the average of a range that meets certain criteria.
This function is like a combination of the AVERAGE and IF functions. With this, you can apply a logical condition to numbers and return the average for numbers that meet that criteria. This allows you more control and gives greater flexibility when averaging numbers in Google Sheets.
This is the syntax of the AVERAGEIF function:
=AVERAGEIF(criteria_range, criterion, [average_range])
- criteria_range – this is the range to check against the criterion
- criterion – this is the condition or test to check the criteria_range for
- average_range – this is the range to average. This is an optional argument. By default, the criteria_range will be used if a different range is not used
Using AVERAGEIF
Text Criteria
To use AVERAGEIF with text criteria you will have to add your text as the criterion argument inside quotation marks.
Here is an example of how this will look:
The formula used in this example is:
=AVERAGEIF(B2:B8,“Clothing”,E2:E8)
Using text as criteria can be useful when you only want to average values that fall in a certain category. In this example, I want to figure out the average revenue generated by clothing items,
So in the formula, the criteria_range is B2:B8. In this range, I will check for the criterion “Clothing”, and if the values match that criteria, the corresponding values in my average_range of E2:E8 will be used to find the average of those values.
Hopefully, now you can begin to see why this function is useful.
Number Criteria
You can also use this function with numbers as criteria. When you use numbers your numeric values used as the criterion argument should not be in quotation marks.
Let’s use the same data as in the previous example to show how this works:
In this example, I have the same data except for this time there is a department number, and I only want to return the average revenue for products in department #1. So in my formula, the criterion is simply the number 1 without any quotation marks.
Expression Criteria
You can also use expressions as your criteria to only return the average of values that are equal to (=), greater than (>), greater than or equal to (>=), less than (<), less than or equal to (<=), or not equal to (<>) any specific number that you put in your formula.
To use an expression as your criteria you will have to place it inside quotation marks.
In this example, I have my expression set to average if the value is greater than 0:
Closing Thoughts
That is it for this function. Hopefully, by now you can see that it is not very difficult to use and is also very useful.
If you’re regularly calculating the average for various datasets you will find that this saves you a lot of time by being able to string logic together in one formula to customize what data gets passed into the average.
More Google Sheets Tutorials:
How to Calculate Weighted Average
How to Use the AVERAGEIFS Function