Excel AVERAGEIFS function with multiple criteria (2024)

AVERAGEIFS function in Excel

The Excel AVERAGEIFS function calculates the arithmetic mean of all cells in a range that meet the specified criteria.

The syntax is as follows:

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

Where:

• Average_range - the range of cells to average.
• Criteria_range1, criteria_range2, … - ranges to be tested against the corresponding criteria.
• Criteria1, criteria2, … - criteria that determine which cells to average. The criteria can be supplied in the form of a number, logical expression, text value, or cell reference.

Criteria_range1 / criteria1 are required, subsequent ones are optional. 1 to 127 range/criteria pairs can be used in one formula.

The AVERAGEIFS function is available in Excel 2007 - Excel 365.

Note. The AVERAGEIFS function works with the AND logic, i.e. only those cells are averaged for which all the conditions are TRUE. To calculate cells for which any single condition is TRUE, use the AVERAGE IF OR formula.

Now that you know the theory, let's see how to use the AVERAGEIFS function in practice.

Excel AVERAGEIFS formula

First, let us outline the generic approach. To construct an AVERAGEIFS formula correctly, please follow these guidelines:

1. In the first argument, supply the range that you want to average.
2. In subsequent arguments, specify range/criteria pairs. The pairs can be arranged in any order, but the criteria always follows the range it applies to.
3. An AVERAGEIFS formula should always contain an odd number of arguments: average_range + one or more criteria_range/criteria pairs.

AVERAGEIFS with text criteria

To get an average of numbers in one column if another column(s) contains certain text, use that text for criteria.

As an example, let's find an average of the "Apple" sales in the "North" region. For this, we make an AVERAGEIFS formula with two criteria:

• Average_range is C3:C15 (cells to average).
• Criteria_range1 is A3:A15 (Items to check) and criteria1 is "apple".
• Criteria_range2 is B3:B15 (Regions to check) and criteria2 is "north".

Putting the arguments together, we get the following formula:

=AVERAGEIFS(C3:C15, A3:A15, "apple", B3:B15, "north")

With criteria in predefined cells (F3 and F4), the formula takes this form:

=AVERAGEIFS(C3:C15, A3:A15, F3, B3:B15, F4)

AVERAGEIFS with logical operators

When the criteria default to "is equal to", the equality sign can be omitted, and you simply put the target text (enclosed in quotation marks) or number (without the quotation marks) in the corresponding argument like shown in the previous example.

When using other logical operators such as "greater than" (>), "less than" (<), not equal to (<>), and others with a number or date, you enclose the whole construction in double quotes.

For example, to average sales greater than zero delivered by 1-Oct-2022, the formula is:

=AVERAGEIFS(C3:C15, B3:B15, "<10/1/2022", C3:C15, ">0")

When the criteria are in separate cells, you enclose a logical operator in quotation marks and concatenate it with a cell reference using an ampersand (&). For example:

=AVERAGEIFS(C3:C15, B3:B15, "<"&F3, C3:C15, ">"&F4)

AVERAGEIFS with wildcard characters

To average cells based on partial text match, use wildcard characters in criteria - a question mark (?) to match any single character or an asterisk (*) to match any number of characters.

In the table below, suppose you wish to average "orange" sales in all "south" regions including "south-west" and "south-east". To have it done, we include an asterisk in the second criteria:

=AVERAGEIFS(C3:C15, A3:A15, F3, B3:B15, "south*")

If a partial text match criteria is input in a cell, then concatenate a wildcard character with the cell reference. In our case, the formula takes this shape:

=AVERAGEIFS(C3:C15, A3:A15, F3, B3:B15, F4&"*")

Average if between two values

To get the average of values that fall between two specific values, use one of the following generic formulas:

Average if between two values, inclusive:

AVERAGEIFS(average_range, criteria_range,">=value1", criteria_range,"<=value2")

Average if between two values, exclusive:

AVERAGEIFS(average_range, criteria_range,">value1", criteria_range,"<value2")

In the 1st formula, you use the greater than or equal to (>=) and less than or equal to (<=) logical operators, so the boundary values are included in the average.

In the 2nd formula, the greater than (>) and less than (<) logical criteria exclude the boundary values from the average.

These formulas work nicely or both scenarios - when the cells to average and the cells to check are in the same column or in two different columns.

For example, to calculate the average of sales between 100 and 130 inclusive, you can use this formula:

=AVERAGEIFS(C3:C15, C3:C15, ">=100", C3:C15, "<=130")

With the boundary values in cells E3 and F3, the formula takes this form:

=AVERAGEIFS(C3:C15, C3:C15, ">="&E3, C3:C15, "<="&F3)

Please notice that in this case we use the same reference (C3:C15) for the 3 range arguments.

To average cells in a given column if the values in another column fall between two values, supply a different range for the average_range and criteria_range arguments.

For instance, to average the sales in column C if the date in column B is between 1-Sep and 30-Oct, the formula is:

=AVERAGEIFS(C3:C15, B3:B15, ">=9/1/2022", B3:B15, "<=10/30/2022")

With cell references:

=AVERAGEIFS(C3:C15, B3:B15, ">="&E3, B3:B15, "<="&F3)

Practice workbook for download

Excel AVERAGEIFS function - examples (.xlsx file)