Excel 2007 AVERAGEIFS Function

The AVERAGEIFS function is used to find the average (arithmetic mean) of multiple ranges of cells that meet specific criteria. This tutorial on Excel's AVERAGEIFS function includes a step by step example.

The syntax for the AVERAGEIFS function is:

=AVERAGEIFS (average_range, criteria_range1, criteria1, criteria_range2, criteria2…)

Average_range - the range of cells to be averaged.

Criteria_range - the range of cells the function is to search.

Criteria - determines whether a cell in the Average_range is to be averaged or not.

Note: Up to 127 range/criteria pairs can be entered into the function.

Example Using Excel's AVERAGE IFS Function:

Note: For help with this example, see the image above.

1. Enter the following data into cells D1 to D4: 80, 100, 120, 140.


2. Enter the following data into cells E1 to E4: 22, 33, 12, 17.


3. Enter the following data into cells F1 to F4: 1, 7, 5, 6.


4. Click on cell G1 - the location where the results will be displayed.


5. Click on the Formulas tab.


6. Choose More Functions > Statistical from the ribbon to open the function drop down list.


7. Click on AVERAGEIFS in the list to bring up the function's dialog box.


8. Click on the Average_range line in the dialog box.


9. Drag select cells E1 to E4 on the spreadsheet to add that range to the Average_range line.


10. Click on the Criteria_range1 line in the dialog box.


11. Drag select cells F1 to F4 on the spreadsheet.


12. Click on the Criteria1 line in the dialog box.


13. Type " > 15 " on the Criteria1 line. This tells the function to only look at data in cells F1 to F4 that is greater than 15.


14. Click on the Criteria_range2 line in the dialog box.


15. Drag select cells G1 to G4 on the spreadsheet.


16. Click on the Criteria2 line in the dialog box.


17. Type " > 5 " on the Criteria2 line. This tells the function to only look at data in cells G1 to G4 that is greater than 5.


18. Click OK.


19. The answer 120 should appear in cell H1. This is because only two of the four rows of data (rows 2 and 4) match both criteria: a number in column F that is greater than 15 and a number in column G that is greater than 5.


20. The AVERAGEIFS function, therefore, averages only the data in cells E2 and E4. The average for the data in these two rows is 120.


21. The complete function:
    = AVERAGEIFS ( E1 : E4 , F1 : F4 , " > 15 " , G1 : G4 , " > 5 " ) appears in the formula bar above the worksheet when you click on cell H1.

• Excel 2007 SUMIF
• Excel 2007 COUNTIF
• Excel IF Function Step by Step Tutorial