One of the most commonly used functions in Google Sheets is AVERAGE. It lets you easily get the numerical mean in a set of data. While it is a very helpful function, it still has limitations.
The AVERAGEIF function in Google Sheets is just like AVERAGE,
In this article we will guide you through multiple examples of the AVERAGEIF Google Sheets functions with different criteria.
Table of Contents
AVERAGEIF Google Sheets Syntax
The AVERAGEIF function in Google Sheets has the following syntax:
=AVERAGEIF(criteria_range, criterion,[average_range])
Let us break down its arguments.
- criteria_range is the set of categories containing the specific criterion whose average will be taken.
- criterion is the condition to be tested against the criteria_range. This can be in the form of number, text, date, or expression.
- average_range is an optional argument. It contains the values to get the average of.
Google Sheets AVERAGEIFย Without an Average Range
If the average_range is not included in the formula, the average will be taken from the criteria_range.
AVERAGEIF Google Sheets Criterion in the Form of Number
Take a look at the image below. The spreadsheet contains the income report of two companies from multiple criteria. As an example, we will get the average income of Company 1.
1. To start, type the following in an empty cell:
=AVERAGEIF(
Youโll see that the syntax will appear on your screen upon typing the function. This happens for all functions in Google Sheets, so thereโs no need to memorize the arguments of every function.
Next, we will select the criteria_range. Theย criteria_rangeย in this example is the data under โCompanyโ, which are Company 1 and 2.
2. Highlight the data under โCompanyโ, cells C3 to C13.
3. Type a comma ( , ) to move on to the next argument.
Youโll see that as you type the comma after the argument, the next argument to be taken becomes highlighted, as shown in the image above.
This is a helpful feature in Google Sheets that lets you avoid confusion, especially when the function you are using contains many arguments.
We will now select the criterion. Our criterionย in this example is โCompany 1โ.
4. Select any cell in the criteria_rangeย that contains โ1โ.
Alternatively, you can type โ1โ instead of clicking on a cell. In this case, your current formula will be:
=AVERAGEIF(C3:13,1
5. Type a comma ( , ) to move on to the last argument.
Our last argument, average_range, is the data under โAmountโ.
6. Highlight the data under โAmountโ, cells D3 to D13.
7. Type a closing parenthesis to complete the formula.
8. Press Enterย to get the average.
Thatโs it! The average income of Company 1 will now show on your screen.
You can do the same steps for getting the average income of Company 2.
AVERAGEIF Google Sheets Criterion in the Form of Text
The criterion argument of the AVERAGEIF function can also be in the form of text. Let us use the previous example but this time, we will get the total income of both companies coming from โSalesโ.
1. Type the function any empty cell:
=AVERAGEIF(
Select the criteria_range. This time, our criteria_rangeย will be the data under โCategory.โ
2. Highlight the data under โCategoryโ, cells B3 to B13.
3. Type a comma ( , ) to move forward to the next argument.
The next argument is the criterion, which is in this case, โSalesโ.
4. Select any cell in the criteria_rangeย that contains โSalesโ.
Like the first example, you can also type โSalesโย instead of clicking on a cell. But, itโs important to take note of the quotation marks (โ โ). Your current formula will now look like this:
=AVERAGEIF(B3:B13,โSalesโ
When typing texts or other characters aside from numbers in a formula, these quotation marks should be included for the formula to work properly. It will result in an error if there are no quotation marks. See the image below.
Here, I typed โSalesโ without quotation marks. You will see that this results in an error. Also, take a look at the color of the word โSales” from this image and the previous image. If the argument is written properly, the text will be colored green. Otherwise, it will be colored black.
5. Type a comma ( , ) to move on to the last argument.
Same as the previous example, our average_range is the data under โAmountโ.
6. Highlight the data under โAmountโ, cells D3 to D13.
7. Type a closing parenthesis to complete the formula.
8. Press Enterย to get the average.
AVERAGEIF Google Sheets Criterion in the Form of Comparison Operators
Aside from numbers and texts, the criterion in the AVERAGEIF function can be in the form of comparison operators. Let us first review what are the comparison operators in Google Sheets.
Comparison Operators in Google Sheets
There are six comparison operators in Google Sheets.
- Equal to ( = ) determines if the data on the right side is equal to the data on the left side.
- Not Equal to ( <> ) compares if the data on the right side is not equal to the data on the left side.
- Less than ( < ) determines if the data on the right side is less than the data on the left side.
- Greater than ( > ) checks if the data on the right side is greater than the data on the left side.
- Less than or Equal to ( <= ) determines if the data on the right side is less than or equal to the data on the left side.
- Greater than or Equal to ( >= ) compares if the data on the right side is greater than or equal to the data on the left side.
Letโs take the previous data. In this example, our objective will be to find the average of the amounts less than 100,000. To start,
1. Type the function into any empty cell:
=AVERAGEIF(
Unlike the previous examples, our criteria_rangeย here will be the data under โAmountโ.
2. Highlight the data under โAmountโ, cells D3 to D13.
3. Type a comma ( , ).
For our criterion, we will get the average of the amounts less than 100,000. To be able to write this inside the formula:
4. Type โ < 100,000 โ. Do not forget to include the quotation marks.
7. Type a closing parenthesis to complete the formula.
Notice that in this example, we have no data for the argument average_range. This is because the average will be taken from the criteria_range.
8. Press Enterย to get the average.
Google Sheets AVERAGEIF Criterion for Date
If you want to calculate the average with the date as the specified criteria, you can input the date directly or get it as a date function.
If the date is entered directly, it should be enclosed in quotation marks in the AVERAGEIF formula, for example
=AVERAGEIF(B2:B12, โ24/10/2022โ, C2:C12)
With the date function, on the other hand, you enclose the date in brackets, for example:
=AVERAGEIF(B2:B12, Date(24/10/2022), C2:C12)
You can also use the cell reference of the date, in which case you donโt need to use quotation marks.
How to Use AVERAGEIF in Google Sheets with Wildcards
There are three key wildcardsย in Google Sheets that function differently with specific functions, including the AVERAGEIF function. They include the question mark?, asterisk*, and the tilde~
They are used in filtering when you want to return specific results.
The question mark character is used to represent a single character, while the asterisk wildcard represents any number of characters. The tilde is used to tell Google Sheets that the character is not a wildcard.
Let’s say you want to calculate the average of a particular brand, for example, Nike, in the sheets below.
Just like above, youโll type in your AVERAGEIF function in the cell and select your criteria range, then the criterion, in this case, will be the brand name, or you can select a cell with the brand name. Finally, select the cells for the average range.
If you add an asterisk at the end of the criteria word, in this case, Nike, then it will calculate the average of all the shoes starting with the word Nike. You can then use the following AVERAGEIF formula in the average quantity cell:
=AVERAGEIF($A$2:$A$9,A12,B2:B9)
You should enclose the criteria range in dollar sign by using the shortcut F4 after selecting the criteria range cells.
This will give you the average quantity of only the Nike shoes.
You can use the question mark wild card similarly but with a single character.
When to Use AVERAGEIF in Google Sheets
As mentioned before, the Google Sheet AVERAGEIF function is usually used when getting the average of values that meet specific criteria. It is very efficient for fast data analysis, and It can be used in sales, performance reports, averaging grades, and many other ways.
If you want to calculate the average data with different levels of importance, you use the weighted averageย instead.
AVERAGEIFS in Google Sheets
The AVERAGEIFS Google Sheets function works the same way as the AVERAGEIF function. The only difference between the two is that Google Sheets AVERAGEIFS returns the average of values that satisfy more than one criterion.
The syntax for AVERAGEIFS would therefore be:
AVERAGEIFS(average range, criteria range1, criterion1, [criteria range2, criterion2, ...])
If you use only one criteria then the AVERAGEIFS function will give the same results as the AVERAGEIF function in Google Sheets.
How Do I AVERAGEIF Multiple Ranges or AVERAGEIF Multiple Criteria?
Sometimes, we encounter spreadsheets with lots and lots of categories, and often, we might be tempted to use Google Sheet ย AVERAGEIF for multiple criteria. But, This is where the AVERAGEIFSย function becomes helpful. This function is the same as AVERAGEIF,ย but it has more arguments. You can use this time-saving function to get the average of the data under multiple criteria.
What to Learn Next
The AVERAGEIF Google Sheets function is a very useful formula that returns the average of values that meet specified criteria. In this article, you have learned how the AVERAGEIF function in Google Sheets works. Additionally, the use of the different criteria (number, text, and comparison operators) was discussed. You can use this function in calculating the average of the sales data, grades, and in other kinds of data analysis.
You can continue your spreadsheet mastery by applying what youโve learned here to other IF functionsย such as SUMIFย and COUNTIF.
