Like all IF functions, AVERAGEIF will only calculate data within a spreadsheet if it meets specific criteria.
The AVERAGEIF function in Google Sheets will calculate the mean of the applicable cells from the columns or rows of the given range of cells.
This Article Covers:
When To Use AVERAGEIF in Google Sheets
You can use AVERAGEIF in several different circumstances. Here are a few examples, although this list is nowhere near exhaustive:
- Figure out the average commission of your top-performing sales employees
- Calculate average losses based on criteria such as accident or theft
- Work out average sales of a product by fiscal period
- Discover the average cost of rent for multiple location cities based on state or city
A Simple Example
Let’s consider a basic example of how AVERAGEIF Google Sheet could be useful.
Say a medical team is recording a trial for heart medication with three groups:
- A control group
- Patients receiving treatment A
- Patients receiving treatment B
During the trial, they regularly keep track of the heart rates of the patients. The scientist could easily use the Google Sheet AVERAGE IF function to calculate the average heart rate for each group simply by selecting the control group, treatment a, or treatment b as a criterion.
Not only that, they could use this function to calculate an average for data that meets any other criteria that may be on the spreadsheet such as gender, above or below a certain weight or age, using other medications, etc.
That’s just a tiny taste of the broad range of applications for the AVERAGEIF function Google Sheets.
Let’s take a look at how this function works.
AVERAGEIF Syntax
The syntax of the Google Sheet AVERAGEIF formula Google Sheets requires at least two arguments of the criteria_range and the criterion. You can also add the average_range for more specific calculations – this part of the syntax will often come up as most calculations that don’t require it can just use the AVERAGE function.
The syntax goes as follows:
=AVERAGEIF(criteria_range, criterion, [average_range])
What Does Each Part Of The Syntax Do?
- = The equal sign signifies to the spreadsheet that we are entering a function
- AVERAGEIF() The function – this contains the arguments and tells Google Sheets what calculation to do with that data
- criteria_range Indicates where to look for the criteria – in our previous example; you would select the cells that have which treatment the patients were receiving
- criterion This argument tells the spreadsheet which criterion in the critera_range must be met to be included in the calculation – in the example, it could be “treatment A”
- average_range This is where you put the range of data you want to work the average out for – in our example, it would be the heart rates of the patients
Take a look at the sample spreadsheet below to show precisely how the spreadsheet would work with our example:
You can see that the Google Sheets AVERAGEIF function calculated the average of only the patents that received Treatment A. We achieved this by substituting the following fields into the syntax:
=AVERAGEIF(B2:B11,”Treatment A”,C2:C11)
If you’re still a little confused, that’s okay. Let’s look at a step-by-step guided example of completing the same calculation for Treatment B patients.
The Full Breakdown
- Select the cell that you want the calculation to show in. C16 in this example.
- Either type out =AVERAGEIF( or click on AVERAGEIF when Google Sheets suggests it
- Click and drag over the criteria range. In this case, it’s the treatment types, so cells B2:B11. You could also manually type this if you’d rather.
- Press the comma button “,”
- Type the criteria inside quotation marks – “Treatment B” for this sample
- Press the comma button again “,”
- Click and drag over or manually type the cell range for the data to be considered for the average. In the example, it is the heart rate data cells, so C2:C11
- Press the closing bracket key “)”
- Press Enter
That’s all you have to do to perform basic AVERAGEIF function in Google Sheets. But, they can get a little more complex.
Comparison Operators and the AVERAGEIF Function
Our above example was much neater than most real-life calculations you may need to make. In reality, you may need to work with only numerical figures and have to specify comparison criteria. That’s why the AVERAGEIF function is compatible with these six comparison operators:
- = (equals)
- <> (not equal to)
- > (greater than)
- < (less than)
- >= (greater than or equal to)
- <= (less than or equal to)
Placing each of these into your syntax will allow more control over your results or complete different calculations.
Let’s use our same spreadsheet and use the <= (less than or equal to) operator to provide an example.
Perhaps the researchers only want the average heart rate of patients 1-5. We can use the <= operator and the Google Sheets AVERAGEIF function to do this.
- This time we are going to select the patient numbers as our criteria_range
- Then enter the comparison operator and numerical value. For this example, that’s <=5 (less than or equal to 5)
- Then select the heart rate data again and press Enter
For another example, let’s exclude patient 3 using the same process and the <> (not equal to) control. So, if we don’t want to include patient 3, we would have to use <>3
You can see that the syntax remains pretty much the same but with a different operator. The operators allow the inclusion or exclusion of data from the calculations.
AVERAGEIF Without an Average Range
If you don’t provide an average_range for the AVERAGEIF function in Google Sheets to work with, it will instead find the average of the criteria range.
Take a look at the example below, where we still used the <=5 operator, but no average range, so the function returned the average of the patient numbers instead.
This can also be a helpful calculation in some circumstances – just something to be aware of.
AVERAGEIFS vs. AVERAGEIF
Sometimes you may try to Google Sheets AVERAGE IF multiple ranges or AVERAGEIF Multiple Criteria. But this won’t work. To try that out, you’ll have to use the AVERAGEIFS Google Sheets function instead.
You can use AVERAGEIFS instead of AVERAGEIF if you need to consider:
- More than one data set
- Multiple criteria
- Multiple columns or rows
- Multiple ranges
Just think of the “S” as pluralizing the function. So if there is more than one of something in your potential formula, use AVERAGEIFS instead.
Unfortunately, the Google Sheets AVERAGEIFS function changes up the order of the syntax when compared to AVERAGEIF. So, despite them being very similar both in function and appearance, they are not compatible functions.
AVERAGEIF for Different Format as Criteria
Sometimes you might need to find the average using criteria that is not a cell reference or a numeric value. For example, finding the average if between two dates in Google Sheets. When combining formulas with text in Google Sheets, we need to put the date values in quotation marks.
When using dates as the criteria, we put them in question marks even though they are numerical. The formula would therefore be:
=AVERAGEIF(Range,"Date criteria", average range)
Let’s look at an example below. We have used the formula:
=AVERAGEIF(A2:A13,"9/1/2022",B2:B13)
to get the average of numbers for 9/1/2022
Frequently Asked Questions
What is the Difference Between AVERAGEIF and AVERAGEIFs?
AVERAGEIF and AVERAGEIFs functions are pretty similar to each other and they both give you the average based on the specified criteria. The difference comes in with the number of criteria. AVERAGEIF only works with one criteria. On the other hand, AVERAGEIFs can operate with multiple criteria.
How Do You Automate Average in Google Sheets?
To automate Average on Google Sheets you can either use Google App Scripts or an add on. You can find add ons in the extensions menu and browse through them
One good addon for automating add on is sheet automation. You can install it and use it to add actions that you can save and use continuously. This way you’ll be able to automate Average in Google Sheets
Can you use AVERAGEIF with Multiple Ranges?
No, you can’t use AVERAGEIF with multiple ranges. AVERAGEIFS function works best with multiple ranges since it allows more arguments than average if Google Sheets function. This is because AVERAGEIF only allows three arguments which include range, criterion argument and average range.
What is an Example of How to Use AVERAGEIF in Google Sheets?
Here’s an example of how to use AVERAGEIF in Google Sheets:
=AVERAGEIF(B2:B11,”Nike”,D2:D11)
In this case we have used the AVERAGEIF function in Google Sheets to find the average cost of Nike shoes.
What are Some Similar Formulae to AVERAGEIF in Google Sheets?
Some similar functions to AVERAGEIF include AVERAGE, MEDIAN, AVERAGE.WEIGHTED, and AVERAGEA:
AVERAGE- Returns the average of the specified range
MEDIAN- Returns the medium of the specified data range of the values are numbers.
AVERAGE WEIGHTED– Returns the average with some factors being more important than others
AVERAGEA- Returns the numerical average value of the specified range. It allows up to 30 arguments.
What to Learn Next
Learning how to master spreadsheet functions before they become necessary can help you get a head start on your business pursuits. The AVERAGEIFS function would be a good one to learn next as it fulfills a similar purpose but for more complicated spreadsheets. If your business already needs to use the AVERAGEIF Google Sheets function, AVERAGEIFS is not far behind.
Outside of that, there are plenty of other valuable spreadsheet functions and operations covered on our site; explore the categories that you think could be beneficial for your business. Stay productive!
Related:
- Google Sheets Help – IF Function.
- How to Use IF Function in Excel.
- How to Sort Data in Google Sheets.
- How to Quickly Split Text in Google Sheets.
- Using COUNTIF Function in Google Sheets.
- Using IFS Function in Google Sheets.
- Using IFERROR function in Google Sheets.
- Using OR function in Google Sheets.
- How to Use SWITCH function in Google Sheets
