The AVERAGEIFS Function of Excel computes the average of the figures in a range that meet one or more delivered conditions. The criteria of the AVERAGEIFS Function also supports the logical operators and wildcard operators in Excel.
What Is The Averageifs Function In Excel?
AVERAGEIFS Function averages cells in a range that is equivalent to the provided condition. AVERAGEIFS Function can apply more than one set of criteria, with more than one range whereas the AVERAGEIF Function can only apply one criteria at a time. Therefore, the purpose of using AVERAGEIFS Function is that it averages cells that match multiple criteria. The first range in AVERAGEIFS Function is the range to be averaged.
What Is The Syntax For Averageifs Function?
The common formula used for AVERAGEIFS Function is-
=AVERAGEIFS (avg_rng, range1, criteria1, [range2], [criteria2], …)
avg_rng – It is the range to average
range1 – range1 is the first range to evaluate
criteria1 – The criteria1 is the criteria to use on range1
range2 – It is the second range to evaluate
criteria2 – criteria2 is the criteria to use on range2
What Is The Example Of Averageifs Function?
AVERAGEIFS Function can be used to average the response times by month by using the formula of the AVERAGEIFS function along with other functions. It can also be used to average the numerical values like price, quantity, percentage, temperature etc. based on more than one criteria.
We have a data for products and their price as shown in picture below. In this case, we have configured AVERAGEIFS to average price of products using the below criteria-
- Price of product is greater than $0
- Price of product is greater than $0 and less than $20
- Price of product whose quantity is greater than 0 and less than 10
Why Is Averageifs Function Used In Excel?
AVERAGEIFS Function is used in Excel when AVERAGEIF Function cannot work out. The AVERAGEIF Function can be applied only on one criteria. But there are situations where you may need to apply more than one set of criteria. To overcome the limitation of AVERAGEIF Function, AVERAGEIFS Function is used in Excel.
Points To Remember
- If no criteria are met, then the AVERAGEIFS Function will return the #DIV0! error value
- It is essential that each additional range should include the same number of rows and columns as the avg_range
- Non-numeric criteria must be fenced in double quotes
- The wildcard characters can be used in criteria
- For each additional criteria, up to 127 range of pairs are allowed
[gdlr_widget_box title=”Conclusion” title-color=”#ffffff” background=”#252525″ color=”#ffffff” font-size=”16px”]
This blog gives the basic idea about the AVERAGEIFS Function in Excel. It is used to build more than one set of criteria. Concatenation with an ampersand (&) is necessary while building criteria based on a cell reference for AVERAGEIFS Function.