Excel AVERAGEIFS: Find the Average for Specific Criteria

01
of 10

How the AVERAGEIFS Function Works

Excel AVERAGEIFS Function Tutorial
© Ted French. Excel AVERAGEIFS Function Tutorial

How the AVERAGEIFS Function Works

The AVERAGEIFS function extends the usefulness of the AVERAGEIF function by allowing you to set conditions on two or more ranges of data rather than just one as in AVERAGEIF.

Normally, AVERAGEIFS works with rows of data called records. In a record, all of the data in each cell or field in the row is related - such as a company's name, address and phone number.

AVERAGEIFS looks for specific criteria in two or more fields in the record and only if it finds a match for each field specified is the data for that record included in the average.

AVERAGEIFS Step by Step Tutorial

In the AVERAGEIF step by step tutorial we averaged the sales for all sales agents in the East sales region for the past year.

In this tutorial we will set a second condition using AVERAGEIFS and only average those sales agents in the East sales region who have made more than 250 sales in the past year.

Following the steps in the tutorial topics below walks you through creating and using the AVERAGEIFS function seen in the image above.

Tutorial Topics

  • Entering the Tutorial Data
  • The AVERAGEIFS Function's Syntax
  • Starting the AVERAGEIFS Function
  • The Average_range Argument
  • The Criteria_range1 Argument
  • The Criteria1 Argument
  • The Criteria_range2 Argument
  • The Criteria2 Argument
  • And the Answer is ..
02
of 10

Entering the Tutorial Data

Entering the Tutorial Data
Entering the Tutorial Data. © Ted French

Entering the Tutorial Data

The first step to using the AVERAGEIFS function in Excel is to enter the data.

Enter the data into cells C1 to E11 of an Excel worksheet as seen in the image above.

Note: The tutorial instructions do not include formatting steps for the worksheet.

This will not interfere with completing the tutorial. Your worksheet will look different than the example shown, but AVERAGEIFS will give you the same results.

Information on formatting options similar to those seen above is available in this Basic Excel Formatting Tutorial.

03
of 10

The AVERAGEIFS Function's Syntax

The AVERAGEIFS Function's Syntax
The AVERAGEIFS Function's Syntax. © Ted French

The AVERAGEIFS Function's Syntax

In Excel, a function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for AVERAGEIFS is:

=AVERAGEIFS (Average_range, Criteria_range1, Criteria1, Criteria_range2, Criteria2, ...)

Note: Up to 127 Criteria_range / Criteria pairs can be specified in the function.

The AVERAGEIFS Function's Arguments

The arguments tell the function what conditions are being tested for and what range of data to average when those conditions are met.

All arguments in this function are required.

Average_range - the data in this range of cells is averaged when a match is found between the Criteria_ranges and their corresponding Criteria arguments - a match must be found for all Criteria defined for a record to be included in the average.

Criteria_range - the group of cells the function is to search for a match to the corresponding Criteria argument.

Criteria - this value is compared with the data in the corresponding Criteria_range. Actual data or the cell reference to the data can be entered for this argument.

04
of 10

Starting the AVERAGEIFS Function

Opening the AVERAGEIFS Function dialog box
Opening the AVERAGEIFS Function dialog box. © Ted French

Opening the AVERAGEIFS Function dialog box

Although it is possible to just type the AVERAGEIFS function into a cell, many people find it easier to use the function's dialog box to add the function to a worksheet.

Tutorial Steps

  1. Click on cell E12 to make it the active cell. This is where we will enter the AVERAGEIFS function.
  2. Click on the Formulas tab of the ribbon.
  3. Choose More Functions > Statistical from the ribbon to open the function drop down list.
  4. Click on AVERAGEIFS in the list to bring up the AVERAGEIFS dialog box.

The data that we enter into the blank lines in the dialog box will form the arguments of the AVERAGEIFS function.

These arguments tell the function what conditions we are testing for and what range of data to average when those conditions are met.

05
of 10

Entering the Average_range Argument

Entering the Average_range Argument
Entering the Average_range Argument. © Ted French

Entering the Average_range Argument

The Average_range argument contains the cell references to the data we want to average.

When the function finds a match between all of the specified Criteria and Criteria_range arguments for a record the Average_range field for that record is included in the average.

 

In this tutorial the data for the Average_range argument is located in the Total Sales column.

Tutorial Steps

  1. Click on the Average_range line in the dialog box.
  2. Highlight cells E3 to E9 in the worksheet to add these cell references to the dialog box.
06
of 10

Entering the Criteria_range1 Argument

Entering the Criteria_range1 Argument
Entering the Criteria_range1 Argument. © Ted French

Entering the Criteria_range1 Argument

In this tutorial we are trying to match two criteria in each data record:

  1. Sales agents from the East sales region.
  2. Sales agents who have made more than 250 sales this year.

The Criteria_range1 argument indicates the range of cells the AVERAGEIFS is to search when trying to match the first criteria - the East sales region.

Tutorial Steps

  1. In the dialog box, click on the Criteria_range1 line.
  2. Highlight cells C3 to C9 in the worksheet to enter these cell references as the range to be searched by the function.
07
of 10

Entering the Criteria1 Argument

Entering the Criteria1 Argument
Entering the Criteria1 Argument. © Ted French

Entering the Criteria1 Argument

In this tutorial the first criteria we are looking to match is if data in the range C3:C9 equals East.

Although actual data - such as the word East - can be entered into the dialog box for this argument it is usually best to add the data to a cell in the worksheet and then enter that cell reference into the dialog box.

Tutorial Steps

  1. Click on the Criteria1 line in the dialog box.
  2. Click on cell C12 to enter that cell reference. The function will search the range selected in the previous step for data that matches this criteria.
  3. The search term (East) will be added to cell C12 in the last step of the tutorial.

Cell References Increase Function Versatility

If a cell reference, such as C12, is entered as the Criteria Argument, the AVERAGEIFS function will look for matches to whatever data has been typed into that cell in the worksheet.

So after finding the average sales for the East region it will be easy to find the average sales for another sales region simply by changing East to North or West in cell C12. The function will automatically update and display the new result.
08
of 10

Entering the Criteria_range2 Argument

Entering the Criteria_range2 Argument
Entering the Criteria_range2 Argument. © Ted French

Entering the Criteria_range2 Argument

As mentioned previously, in this tutorial we are trying to match two criteria in each data record:

  1. Sales agents from the East sales region.
  2. Sales agents who have made more than 250 sales this year.

The Criteria_range2 argument indicates the range of cells the AVERAGEIFS is to search when trying to match the second criteria - sales agents who have sold more than 250 orders this year.

Tutorial Steps

  1. In the dialog box, click on the Criteria_range2 line.
  2. Highlight cells D3 to D9 in the worksheet to enter these cell references as the range to be searched by the function.
09
of 10

Entering the Criteria2 Argument

Entering the Criteria2 Argument
Entering the Criteria2 Argument. © Ted French

Entering the Criteria2 Argument

In this tutorial the second criteria we are looking to match is if data in the range D3:D9 is greater than 250 sales orders.

As with the Criteria1 argument, we will enter the cell reference to Criteria2's location into the dialog box rather than the data itself.

Tutorial Steps

  1. Click on the Criteria2 line in the dialog box.
  2. Click on cell D12 to enter that cell reference. The function will search the range selected in the previous step for data that matches this criteria.
  3. Click OK to complete the AVERAGEIFS function and close the dialog box.
  4. A #DIV/0! error will appear in cell E12 - the cell where we entered the function - because we have not yet added the data to the Criteria1 and Criteria2 fields (C12 and D12).
  5. This data will be added in the next step of the tutorial.
10
of 10

Adding the Search Criteria and Completing the Tutorial

Adding the Search Criteria
Adding the Search Criteria. © Ted French

Adding the Search Criteria and Completing the Tutorial

The last step in the tutorial is to add data to the cells in the worksheet identified as containing the Criteria arguments.

Tutorial Steps

  1. In cell C12 type East and press the Enter key on the keyboard.
  2. In cell D12 type >250 and press the Enter key on the keyboard (the " > " is the symbol for greater than in Excel) .
  3. The answer $74,220 should appear in cell E13.
  4. Only two records those in rows 4 and 5 match both criteria and, therefore, only the sales totals for those two records are averaged.
  5. The average of $70,702 and $77,738 is $74,220.
  6. When you click on cell E12, the complete function
    =AVERAGEIFS (E3:E9, C3:C9, C12, D3:D9, D12) appears in the formula bar above the worksheet.
Format
mla apa chicago
Your Citation
French, Ted. "Excel AVERAGEIFS: Find the Average for Specific Criteria." ThoughtCo, Dec. 11, 2016, thoughtco.com/excel-averageifs-find-the-average-f-3124004. French, Ted. (2016, December 11). Excel AVERAGEIFS: Find the Average for Specific Criteria. Retrieved from https://www.thoughtco.com/excel-averageifs-find-the-average-f-3124004 French, Ted. "Excel AVERAGEIFS: Find the Average for Specific Criteria." ThoughtCo. https://www.thoughtco.com/excel-averageifs-find-the-average-f-3124004 (accessed January 20, 2018).