Updated July 5, 2023
Excel AVERAGEIF (Table of Contents)
Introduction to AVERAGEIF in Excel
The AVERAGEIF function in Excel calculates the average of numbers based on defined criteria. For example, we have sales data for 4 products, and we want to find out the average sales of any product from the entire data or a selected portion. For that, we use AVERAGEIF. Per syntax, we first select the range of all the products and then give the criteria by selecting the product name for which we want to find average sales and then select the complete range of sold quantities.
Now we know the exact AVERAGEIF function, and we will learn how to write a formula using the formula’s function and syntax.
The syntax of the AVERAGEIF function
Here is the explanation of all the elements of the syntax.
- Range – A range of cells on which the criteria or condition is applied. The range can also include a number, cell references, and names. If there is no “Average-range” argument, this range will be used to calculate the average.
- Criteria – This is a condition based on which cells will be averaged. It could be a number, cell reference, text value, logical statement (like “<5”), or expression.
- average_range – The cell range to average. It is optional. In the absence of an average range, the range is used to calculate the average.
How to use the AVERAGEIF Function in Excel?
Now, we will learn how to use the AVERAGEIF function in Excel with the help of various examples. It is an inbuilt function in Excel.
AVERAGEIF in Excel – Example #1
The AVERAGEIF function in Excel calculates the average of cells that exactly match the criteria or the condition specified.
Below is a table containing the product name and the sold units.
In this example, we need to find the average units sold for product B.
We can directly type the formula by starting it with = and typing AVERAGEIF, or we can also select the function from the ribbon as shown below:
Now Function Arguments Box will appear. Then select Range: A2:A13, Criteria: B15, Average_range: B2:B13.
Press OK. We get the below result: the average number of units sold for product B.
We can also type in “B” instead of cell reference in the formula as shown below in cell B15:
Explanation of the example :
Range: We have taken A2: A13 as the range on which our criteria (B) will be applied.
Criteria: Since we wanted to know the average units sold for B, so B is our criteria.
Average _range: B2: B13 is the range of cells from which Excel will average.
We are taking our last example. Suppose we want to know the average units sold for D products.
We will get the average units sold for D product = 194.33
AVERAGEIF in Excel – Example #2
We have a sample data table containing students’ scores for the second and third semesters.
Now, if we want to calculate the average second-semester score from the above data, we can do it by writing the formula as shown below:
We will write B2:B7 as range and make it absolute by pressing F4. Then, we will write “second” as criteria and C2:C7 as average_range and make it an absolute range. We will get the average third-semester score by using cell reference B6 instead of the word “Third” as criteria.
And we will press the Enter key to get the result.
AVERAGEIF in Excel – Example #3
This sample data shows sales data for cities in different zones.
If we want to find the average sale of a particular zone, say, for instance, West. We can get average sales as below:
Press the Enter key to get the result.
These examples must have given you a fair understanding of using the AVERAGEIF function.
AVERAGEIF in Excel – Example #4
AVERAGEIF using a wildcard in-text criteria
We use a wildcard in the AVERAGEIF function when the text criteria are partially met, preceded, or followed by any other word.
If we want to know the average sales for all cakes instead of a specific cake, we then use a wildcard(*). In our case, the keyword cake is preceded by other words like chocolate, fruit, plain, etc. We can overcome this limitation by using an asterisk sign before the word “cake” to fetch the cakes’ sales data. Below is a sample sales data of confectionary items.
Similarly, we can add an asterisk sign after the search keyword if another word follows it.
So we will write the formula below and add the * sign before our criteria. In this case, our criteria are cakes.
After pressing the Enter key, we see the result.
If any other word follows the keyword criteria, add an asterisk sign after the keyword /criteria. Like “cake*.”
Likewise, if you want to average the sales of all other items apart from cakes, you can use the below formula using a wildcard. Which is “<>*cake”.
Press the Enter key to get the result.
AVERAGEIF in Excel – Example #5
AVERAGEIF using logical operations or numeric criteria
Many a time, we have to average cells based on logical statements like “>100” or “<100”. Or as per numeric criteria. How will we do this?
We will understand this with the help of an example.
Taking our sample data. Suppose we want to know the average of units sold above 250. We will find this out as below:
After pressing the Enter key, we will get a result, as shown below.
So the average of units sold above 250 belongs to products E and D. Similarly, if we want to know the average units sold below 100. We can find this as shown below:
After pressing the Enter key will give us the result.
This is how we can use AVERAGEIF with logical statements as criteria.
Things to Remember
- If the criteria for calculating the average numbers are text or logical expressions, they should always be written in double quotes.
- If no cell in the range meets the criteria for average or the range is empty, the function will return #DIV0! Error.
- If the cell as criteria is blank, the function will take it as a 0.
- Excel treats the default “criteria” operand as equals; other operands may include >,<,<>, >=, and <= as well.
- Suppose any of the cells in a range has True or False. The function will ignore it.
Recommended Articles
This has been a guide to AVERAGEIF in Excel. Here we discuss calculating the Average using AVERAGEIF Function in Excel, practical examples, and a downloadable Excel template. You can also go through our other suggested articles–
