Special

Excel Functions

2003-2010

Special

Excel Functions

2007-2010

Lesson 22: AVERAGEIFS Function (Excel 2007)

The general format for the AVERAGEIFS formula is as follow: =AVERAGEIFS(range where criteria applies, criteria, average of what). The first 2 arguments can be repeated imposing conditions on as many fields (columns) as you need

In this example using the AVERAGEIFS in Excel a first criteria applies to the name field (John) and the second criteria applies to the range that is averaged:
=AVERAGEIFS(C2:C7,"John" ,D2:D7,">5",D2:D7)
In plain English it says calculate the average of cells D2 to D7 if they are greater than 5 and if the name "John" apprears in column C. The result should be 8.

WARNING: You can only use the AVERAGEIFS function if you are using Excel 2007 and if everyone who will receive your workbook is also using Excel 2007. You can do the same thing and even more in a version of Excel that is earlier than 2007. You will use SUMPRODUCT the most powerful function in Excel.

Excel Functions and Formulas Sitemap

Go to the next lesson:
Lesson 23: COUNTIFS (Excel 2007)