This tutorial shows how to use the Excel AVERAGEIFS function for calculating an average with multiple conditions.
When it comes to calculating an arithmetic mean of a group of numbers in Excel, AVERAGE is the way to go. To average cells that meet a certain condition, AVERAGEIF comes in handy. To find an average with multiple criteria, AVERAGEIFS is the function to use. To learn how it works, please keep reading!
The Excel AVERAGEIFS function calculates the arithmetic mean of all cells in a range that meet the specified criteria.
The syntax is as follows:
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)Where:
Criteria_range1 / criteria1 are required, subsequent ones are optional. 1 to 127 range/criteria pairs can be used in one formula.
The AVERAGEIFS function is available in Excel 2007 - Excel 365.
Note. The AVERAGEIFS function works with the AND logic, i.e. only those cells are averaged for which all the conditions are TRUE. To calculate cells for which any single condition is TRUE, use the AVERAGE IF OR formula.
To get a clear understanding of how the function works and avoid errors, take notice of the following facts:
Now that you know the theory, let's see how to use the AVERAGEIFS function in practice.
First, let us outline the generic approach. To construct an AVERAGEIFS formula correctly, please follow these guidelines:
To get an average of numbers in one column if another column(s) contains certain text, use that text for criteria.
As an example, let's find an average of the "Apple" sales in the "South" region. For this, we make an AVERAGEIFS formula with two criteria:
Putting the arguments together, we get the following formula:
=AVERAGEIFS(C3:C15, A3:A15, "apple", B3:B15, "south")
By default, text criteria are case-insensitive, allowing you to type them in any letter case. For instance, "apple," "Apple," and "APPLE" will all be treated as equivalent.
With criteria in predefined cells (F3 and F4), the formula takes this form:
=AVERAGEIFS(C3:C15, A3:A15, F3, B3:B15, F4)
When the criteria default to "is equal to", the equality sign can be omitted, and you simply put the target text (enclosed in quotation marks) or number (without the quotation marks) in the corresponding argument like shown in the previous example.
When using other logical operators such as "greater than" (>), "less than" (), and others with a number or date, you enclose the whole construction in double quotes.
For example, to average sales greater than zero delivered by 1-Oct-2022, the formula is:
=AVERAGEIFS(C3:C15, B3:B15, "0")
When the criteria are in separate cells, you enclose a logical operator in quotation marks and concatenate it with a cell reference using an ampersand (&). For example:
=AVERAGEIFS(C3:C15, B3:B15, ""&F4)
To average cells based on partial text match, use wildcard characters in criteria - a question mark (?) to match any single character or an asterisk (*) to match any number of characters.
In the table below, suppose you wish to average "orange" sales in all "south" regions including "south-west" and "south-east". To have it done, we include an asterisk in the second criteria:
=AVERAGEIFS(C3:C15, A3:A15, F3, B3:B15, "south*")
If a partial text match criteria is input in a cell, then concatenate a wildcard character with the cell reference. In our case, the formula takes this shape:
=AVERAGEIFS(C3:C15, A3:A15, F3, B3:B15, F4&"*")
To get the average of values that fall between two specific values, use one of the following generic formulas:
Average if between two values, inclusive:
AVERAGEIFS(average_range, criteria_range,">=value1", criteria_range,"value2")Average if between two values, exclusive:
AVERAGEIFS(average_range, criteria_range,">value1", criteria_range,"value2")In the 1st formula, you use the greater than or equal to (>=) and less than or equal to (
In the 2nd formula, the greater than (>) and less than (
These formulas work nicely or both scenarios - when the cells to average and the cells to check are in the same column or in two different columns.
For example, to calculate the average of sales between 100 and 130 inclusive, you can use this formula:
=AVERAGEIFS(C3:C15, C3:C15, ">=100", C3:C15, "
With the boundary values in cells E3 and F3, the formula takes this form:
=AVERAGEIFS(C3:C15, C3:C15, ">="&E3, C3:C15, "
Please notice that in this case we use the same reference (C3:C15) for the 3 range arguments.
To average cells in a given column if the values in another column fall between two values, supply a different range for the average_range and criteria_range arguments.
For instance, to average the sales in column C if the date in column B is between 1-Sep and 30-Oct, the formula is:
=AVERAGEIFS(C3:C15, B3:B15, ">=9/1/2022", B3:B15, "
With cell references:
=AVERAGEIFS(C3:C15, B3:B15, ">="&E3, B3:B15, "
<p><img src="/static/imghw/default1.png" data-src="https://img.php.cn/upload/article/000/000/000/174286795884761.png" class="lazy" alt="Excel AVERAGEIFS function with multiple criteria" ></p>
<p>That's how you use the AVERAGEIFS function in Excel to find an arithmetic mean with multiple criteria. I thank you for reading and hope to see you on our blog next week!</p>
<h2>Practice workbook for download</h2>
<p> Excel AVERAGEIFS function - examples (.xlsx file)</p>
The above is the detailed content of Excel AVERAGEIFS function with multiple criteria. For more information, please follow other related articles on the PHP Chinese website!
What are the C language programming software?
The memory cannot be written solution
How to unlock the password lock on your Apple phone if you forget it
How to use python for loop
java
google maps api
How to use each function in js
The difference between wildcard masking and unmasking
How to install the pycharm interpreter
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
