How to Get the Average of All Numbers That Meet Two or More Conditions in Excel Formula

Sometimes, you may want to calculate the average of a set of numbers that meet more than one condition. For example, you may want to find the average sales of a certain product in a certain region, or the average score of students who passed a certain exam. In this article, I will show you how to use the AVERAGEIFS function in Excel to get the average of all numbers that meet two or more conditions.

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

= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Where:

• average_range is the range of cells to average.
• criteria_range1, criteria_range2, … are the ranges to be tested against the corresponding criteria.
• criteria1, criteria2, … are the criteria that determine which cells to average. The criteria can be supplied in the form of a number, expression, cell reference, or text that defines which cells are averaged. For example, criteria can be expressed as 32, “32”, “>32”, “apples”, or B4.

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.

Procedures

To use the AVERAGEIFS function to get the average of all numbers that meet two or more conditions, follow these steps:

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.
4. Enter the formula in a cell and press Enter.

Explanation

To illustrate how the AVERAGEIFS function works, let’s look at an example. Suppose you have a table of sales data as shown below, and you want to calculate the average sales of each product in each region.

To get the average sales of product A in the North region, you can use the following formula:

= AVERAGEIFS (C2:C13, A2:A13, "A", B2:B13, "North")

Where:

• C2:C13 is the average_range, which contains the sales values to average.
• A2:A13 is the first criteria_range, which contains the product names.
• "A" is the first criteria, which specifies that only product A should be averaged.
• B2:B13 is the second criteria_range, which contains the region names.
• "North" is the second criteria, which specifies that only the North region should be averaged.

The formula returns 150, which is the average of the sales values that meet both criteria (100 and 200).

To get the average sales of product B in the East region, you can use the same formula, but change the criteria to “B” and “East”:

= AVERAGEIFS (C2:C13, A2:A13, "B", B2:B13, "East")

The formula returns 220, which is the average of the sales values that meet both criteria (220).

You can also use cell references instead of hard-coded values for the criteria. For example, if you have a summary table like this:

You can enter the following formula in cell D2 and copy it down and across:

= AVERAGEIFS ($C$2:$C$13, $A$2:$A$13, $A2, $B$2:$B$13, B$1)

Where:

• $C$2:$C$13 is the average_range, which is fixed with absolute references.
• $A$2:$A$13 is the first criteria_range, which is fixed with absolute references for the column.
• $A2 is the first criteria, which is fixed with absolute references for the row.
• $B$2:$B$13 is the second criteria_range, which is fixed with absolute references for the column.
• B$1 is the second criteria, which is fixed with absolute references for the row.

The formula returns the average sales of each product in each region, as shown below:

Other Approaches

Besides the AVERAGEIFS function, there are other ways to get the average of all numbers that meet two or more conditions in Excel. Here are some of them:

• Use the AVERAGE and IF functions together. For example, to get the average sales of product A in the North region, you can use this formula:

= AVERAGE (IF ((A2:A13="A") * (B2:B13="North"), C2:C13))

This formula works by using the IF function to return an array of sales values that meet both criteria, and then using the AVERAGE function to calculate the average of that array. Note that this is an array formula, so you need to press Ctrl + Shift + Enter to enter it.

• Use the SUMPRODUCT and COUNTIF functions together. For example, to get the average sales of product A in the North region, you can use this formula:

= SUMPRODUCT ((A2:A13="A") * (B2:B13="North") * C2:C13) / COUNTIF (A2:A13, "A")

This formula works by using the SUMPRODUCT function to multiply the sales values by the criteria and sum up the results, and then dividing by the count of product A using the COUNTIF function.

• Use a pivot table. A pivot table is a powerful tool that can summarize and analyze data in various ways. To get the average sales of each product in each region, you can create a pivot table like this:

1. Select the data range (A1:C13) and go to the Insert tab > PivotTable.
2. In the Create PivotTable dialog box, choose where you want to place the pivot table and click OK.
3. In the PivotTable Fields pane, drag the Product field to the Rows area, the Region field to the Columns area, and the Sales field to the Values area.
4. Right-click any cell in the Values area and choose Summarize Values By > Average.

You will get a pivot table that shows the average sales of each product in each region, as shown below:

You can also use the filters and slicers to change the criteria and view different results.