Excel Average Based on Multiple Criteria

In Excel, you can use the SUMIF and COUNTIF functions, to sum and count values, based on criteria. Did you know that you can also calculate an average, based on criteria?

Average with One Criterion

If you only have one criterion, you can use the AVERAGEIF function. In the screen shot below, the average quantity is calculated for any orders where Pens were sold.

=AVERAGEIF(A1:A10,"Pen",B1:B10)

averageif01

Average for Multiple Criteria

If you need multiple criteria for the average, and you’re using Excel 2007 or a later version, you can use the AVERAGEIFS function.

In the next screen shot, the AVERAGEIFS formula checks for Pen in column A, and a quantity greater than or equal to 10, in column B. The criteria values are entered on the worksheet – cells D3 and E3 – where they can be changed easily.

For the rows that meet those criteria – rows 6 and 10 — an average quantity is calculated.

=AVERAGEIFS(B2:B10,A2:A10,D3,B2:B10,">="&E3)

averageifs01

Download the Sample File

To see these average formulas, and other examples, you can download the sample file from my Contextures website: Excel Average Formulas. The sample file is zipped, and is in xlsx format (Excel 2007 and later versions).

__________