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], …)

Where,

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.

Example:

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

Excel Averageifs function (1)

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.

Recommended Blog:

AVERAGEIF Function
AND Function
ABS Function

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

Conclusion

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.

Pre-Registration Open

Power BI Dax

Related Tutorials

Text To Column in Excel | Split Cells
January 4, 2019
Pivot Tables (Complete Guidelines)
December 27, 2018
Power Bi Dax Deduplication Based On Column
December 17, 2018
Special Character Symbol List with Shortcodes in Excel
December 15, 2018
How to use AVERAGEIF FUNCTION
December 10, 2018
How to use ABS Function in Excel
December 10, 2018
How to use AND Function in Excel
December 10, 2018
Delete Duplicate in Excel or Remove Duplicate in Excel
November 9, 2018
Excel Formulas PDF
September 6, 2018
How To Lock Cells in Excel | Unprotect Excel
August 13, 2018
4x Faster at Excel
August 6, 2018
Separate Content of One Excel Cells into Separate Columns
August 3, 2018
How to Transpose Excel Columns to Rows | Paste Special Method
July 26, 2018
How to create sparklines in Excel
July 19, 2018
AutoSum in Excel with Shortcut
July 17, 2018
OFFSET Function in Excel
July 6, 2018
Strikethrough Shortcut in Excel & Word
July 4, 2018
INDIRECT Function with SUM, MAX, MIN & Independent Cell Value
June 29, 2018
Pivot Table Slicers In Excel
June 12, 2018
How to Wrap Text in Excel Automatically and Manually
June 6, 2018
How to Hide/Unhide Column in Excel
June 5, 2018
Highlight row based on cell value
June 4, 2018
Learn how to remove blank cells in Excel
June 3, 2018
How to Group Numbers, Dates & Text in Pivot table in Excel
June 1, 2018
5 Powerful Tricks to Format cells in Excel
May 31, 2018