Excel AVERAGEIFS Quick Tutorial
Excel AVERAGEIFS Function Quick Tutorial
© Ted French
Related Tutorial: Step by Step Tutorial with Screen Shots - Excel AVERAGEIFS Function
How the AVERAGEIFS Function Works
The AVERAGEIFS function extends the AVERAGEIF function by allowing you to set conditions on two or more ranges of data rather than just the one allowed 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 Function Syntax
The syntax for AVERAGEIFS is:
=AVERAGEIFS (Average_range, Criteria_range1, Criteria1, Criteria_range2, Criteria2, ...)
Up to 127 Criteria_range / Criteria pairs can be specified in the function.
The AVERAGEIFS Function's Arguments
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 - the value we are trying to match in the data record. Actual data or the cell reference to the data can be entered for this argument.
Excel AVERAGEIFS Function Example
For help with this example, see the image above.
The two criteria we will try to match in the sample storm records are:
- wind speeds of greater than 25
- pressure readings that are less than 100
For records that match both conditions, we will find the average rainfall that fell during these events.
Entering the Sample Data
- Enter the following data into cells D2 to D5: Wind Speed, 27, 12, 13, 29.
- Enter the following data into cells E2 to E5: Pressure, 99.8, 101.7, 102.8, 99.6.
- Enter the following data into cells F2 to F5: Rainfall, 1.4, 0.6, 0.9, 1.6.
Entering the Criteria to match
- To make it easier to interpret the results of the function, re-enter the headings in cells D6 to F6: Wind Speed, Pressure, and Rainfall.
- In cell D7 enter > 25. This is the wind speed criteria we are trying to match.
- In cell E7 enter < 100. This is the pressure criteria we are trying to match.
Entering the AVERAGEIFS Function
- Click on cell F7 to make it the active cell. This is where we will enter the AVERAGEIFS function.
- Click on the Formulas tab of the ribbon.
- Choose More Functions > Statistical from the ribbon to open the function drop down list.
- Click on AVERAGEIFS in the list to bring up the AVERAGEIFS dialog box.
- Click on the Average_range line in the dialog box.
- Drag select cells F2 to F5 in the worksheet to add these cell references to the dialog box.
- Click on the Criteria_range1 line in the dialog box.
- Drag select cells D2 to D5 in the worksheet to add these cell references to the dialog box.
- Click on the Criteria1 line in the dialog box.
- Click on cell D7 to enter the cell reference.
- Click on the Criteria_range2 line in the dialog box.
- Drag select cells E2 to E5 in the worksheet to add these cell references to the dialog box.
- Click on the Criteria2 line in the dialog box.
- Click on cell E7 to enter the cell reference.
- Click OK.
- The dialog box will close and the answer 1.5 should be present in cell F7.
Since only the records in rows 2 and 5 match both criteria - that of a wind speed greater than 25 and a pressure reading of less than 100 - only the rainfall amounts for these two records are averaged by the function.
The average of 1.4 and 1.6 is 1.5.
The complete function =AVERAGEIFS (F2:F5, D2:D5, D7, E2:E5, E7) appears in the formula bar above the worksheet when you click on cell F7.
Related Quick Tutorials