Excel Digest

To average cells based on multiple criteria in Excel 2007 you use the AVERAGEIFS function.

Syntax

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

Average_range is the range of cells to average, including numbers or names, arrays, or references that contain numbers.

Criteria_range1, criteria_range2, … are 1 to 127 ranges in which to evaluate the associated criteria.

Criteria1, criteria2, … are 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be averaged.

Example:

In this example we will average the total sales for Jim in quarter 2 (olive green rows). So our criteria are:

1. “Jim” in column B.
2. “Q2″ in column C.

Our formula will be like this:

=AVERAGEIFS(D2:D23;B2:B23;”Jim”;C2:C23;”Q2″)

This formula returns a value of 1094, which is (2540+1250+178+408)/4

Notes:

1. If average_range is a blank or text value, AVERAGEIFS returns the #DIV0! error value.
2. If a cell in a criteria range is empty, AVERAGEIFS treats it as a 0 value.
3. Each criteria_range must be the same size and shape as sum_range.
4. If there are no cells that meet all the criteria, AVERAGEIFS returns the #DIV/0! error value.