How to Use AVERAGEIF in Excel? With 5 Different Criteria
(Note: This guide on how to use AVERAGEIF in Excel is suitable for all Excel versions including Office 365)
The AVERAGEIF in Excel calculates the average of all numbers in a range of cells that meets the given criteria. AVERAGEIF includes logical operators (>,<,<>,==) and wildcards (*,?) for partial matching. It is categorized under Statistical Function.
You’ll Learn:
- Syntax
- Parameters
- How to Use AVERAGEIF
- Double Quotes (“ “) Criteria
- Ignore #N/A Errors
- Named Range
- Ignore Zero Values
- Things to Remember
Related Reads:
How to Calculate Factorial in Excel? Along with 2 Easy Examples
How to Create an Excel Slicer? 2 Easy Ways
IFERROR Excel-The Ultimate Guide to Catching Errors in Excel
Syntax
| =AVERAGEIF(range, criteria, [average_range]) |
Parameters
Range – This is the range of cells where you want to find the average. It is a mandatory parameter and includes numbers, names, arrays, or references that contain numbers.
Criteria – This specifies how the cell will be averaged. Criteria can be an expression, number, cell reference, or text that defines the averaged cells. It is a mandatory parameter.
Average_range – This is the basic set of cells you wish to average. It is an optional parameter and if the user omits it, the function will use the range given.
How to Use AVERAGEIF in Excel
AVERAGEIF in Excel is used in finding the average of cells that are an exact match of given criteria. To understand the uses of the AVERAGEIF function, let’s consider a few examples.
AVERAGEIF with Criteria
The formula to find the average sales from the table below is, =AVERAGEIF(A2:A6,” Texas”, B2:B6). The function considers the range of cells from A2:A6, compares it with the criteria “Texas”, and then returns the sales of that location.
AVERAGEIF with Wildcard Character – Asterisk(*)
Here, the AVERAGEIF function calculates the average of the values in B2:B6 that correspond to the given condition in the range A2:A6.
An asterisk(*) is used to check the condition that matches either zero or more characters. In the example below, ‘*York’ represents that there can be either zero or more characters before ‘York’.
AVERAGEIF with Wildcard Character – Question Mark(?)
The AVERAGEIF function below calculates the average of all values in the range B2:B6 if the corresponding cells in the range A2:A6 contain exactly 5 characters. A question mark (?) matches exactly one character.
Here, a series of five-question marks specify that the function is looking for a five-lettered word, which is Texas.
AVERAGEIF – Skip a Value and Calculate the Average for the Rest
The AVERAGEIF function calculates the average of all values in B2:B6 except ‘Illinois’. The formula =AVERAGEIF(A2:A6,”<>Illinois”, B2:B6), says to calculate the average of sales except for the city ‘Illinois’ mentioned in the criteria.
AVERAGEIF with Cell Reference
A value from another cell can be included in the criteria using the concatenation operator. In the example below, AVERAGEIF will return the average of numbers in B2:B6 that are less than equal to the value in cell C8. Notice that the less than equal to the operator is enclosed in quotes.
Here, the function =AVERAGEIF(B2:B6,”<=”&C8) considers the value in cell C8 which is $6000. The criteria mentioned in the function is any value less than or equal to 6000 in the table. Therefore, it considers the sales in all branches except Illinois and returns the average.
Also Reads:
How to Use the ROUND Function in Excel?
How to Count Unique Values in Excel? 3 Easy Ways to Count Unique and Distinct Values
How to Sort Dates in Excel? 6 Easy Methods
Double Quotes (“ “) Criteria
The text values are enclosed in double-quotes (“ ”) whereas the numbers are not. When a logical operator is included with a number, the number and operator must be enclosed in quotes.
Ignore #N/A Errors
AVERAGEIF in Excel can be made to ignore #N/A errors and return the average value. Apply criteria to filter values in a range to ignore #N/A errors before they are averaged. For example, to specifically ignore #N/A errors, use this formula:
=AVERAGEIF(range,”<>”#N/A)
Or
=AVERAGEIF(named_range,”<>#N/A)
This would work fine if the range contains #N/A errors but will fail if there are other errors in the data.
Named Range
A named range is one or more cells with a given name. Named ranges make formulas easier to read and understand. Select the cells that contain the values for calculation, then click on the Name box and give a name to the selected cells.
In the example below, the cells from B2:B7 are selected and named Q1_sales. You can refer to the cells as Q1_sales whenever you need to find the MIN(), MAX(), AVERAGE(), and AGGREGATE() values.
In this table, the cells C2:C7 are selected and named Q2_sales in the Name box.
Using a named_range is just another way of referencing the cells from B2:B7. The example below shows how AVERAGEIF ignores #N/A errors using named_range to reference the cells. The formula, =AVERAGEIF(Q1_sales,”<>#N/A”) considers the values in the cells from B2:B7 excluding #N/A and returns the average.
Ignore Zero Values
There might be a situation when a branch is opened recently and there have hardly been any sales in that branch and the value is zero. In such a case, you can ignore zero values and calculate the average for the stores that have recorded sales.
The AVERAGEIF with named_range used to select numbers greater than zero is, =AVERAGEIF(named_range,”>=0”). This works fine as long as the numbers are not negative.
In the example above, the sales in 4 places alone are considered and the average value is returned as $4237.5. If you don’t exclude the city that had no sales, then the average sales would be $3390 as it considers the sales in 5 cities and returns an average accordingly.
Things to Remember
- #DIV0! Error – occurs when there are no cells in the range meeting the criteria, blank, or a text value.
- AVERAGEIF treats it as 0 when the given criteria are empty.
- Using wildcard characters such as * and? enhances the search criteria.
- AVERAGEIF will ignore cells that contain TRUE or FALSE and empty cells.
- AVERAGEIF in Excel treats a criteria cell that is empty as 0.
- The wildcard character ‘?’ matches any single character and an ‘*’ matches a sequence of characters. Type a tilde(~) character to find an actual question mark or asterisk.
Suggested Reads:
How to Group in Pivot Table? ( 2 Easy Methods)
How to Create an Excel Map Chart from Pivot Table Data? 3 Simple Steps
How to Custom Sort Excel Data? 2 Easy Steps
Closing Thoughts
In this guide, we explained how to calculate the average of the given numbers with criteria using AVERAGEIF in Excel. It will be of great help when you deal with a huge list of numbers that has random #N/A, zero’s, and negative values that you need to eliminate to view the exact average.
Please visit our free resources center to know more about other logical and statistical functions.
We have high-quality Excel guides and you will be really excited to know how handy and useful these functions are.
Simon Sez IT has been teaching Excel for over ten years. For a low monthly fee, you can access 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
