How to Use AVERAGEIFS Formula - Multiple Conditions in Excel

Calculating the average is one of the frequently used operations in the versatile analytical tool—Excel. The program includes the AVERAGEIFS function, which not only calculates the arithmetic mean but also significantly enhances this operation by allowing selective calculation with multiple conditions.



How to Find the Average Value with Multiple Conditions in Excel

Below is an image of the results from the 1972 Winter Olympics that need to be analyzed. In this example, let’s say we need to calculate the average based on several conditions. The selection criteria are defined by values recorded in separate cells: country, discipline, and medal. These are located in an additional table to create a query for the data. We need to calculate the average only for the results that meet these query criteria from the main list of results in the original table. The formula looks like this:

=AVERAGEIFS(D2:D19,C2:C19,H1,A2:A19,"*"&H2&"*",E2:E19,H3)
AVERAGEIFS Formula.

As a result, we selected the data from the main list based on three criteria and calculated their average.

The AVERAGEIFS function has a structure very similar to the SUMIFS function. The first argument is the range of values to average, followed by pairs of arguments: Criteria_range1;Criteria1, Criteria_range2;Criteria2... and so on. There can be up to 127 such pairs. In this example, we use three pairs of criteria:

  1. C2:C19;H1 – This condition selects only the rows that contain the country name "Switzerland."
  2. A2:A19;"*"&H2&"*" – The second condition selects only those cells from the "Discipline" column that contain the word "Women's".
  3. E2:E19;H3 – This condition selects only the rows containing gold medals.

Alternative Formula for the Arithmetic Mean with Conditions

Typically, there are several ways to solve any given task in Excel. Can we replace or work around the AVERAGEIFS function? It can be replaced with a formula that combines SUMIFS and COUNTIFS. The formula is as follows:

=SUMIFS(D2:D19,C2:C19,H1,A2:A19,"*"&H2&"*",E2:E19,H3)/COUNTIFS(C2:C19,H1,A2:A19,"*"&H2&"*",E2:E19,H3)
SUMIFS and COUNTIFS Formula.

Download formula examples for using AVERAGEIFS with multiple conditions in Excel download file

The result will be the same for calculating the average based on multiple conditions.

Note the similarity of the values in the arguments of all three functions. Where possible, it's better to use the AVERAGEIFS function since it allows you to make changes to the criteria in one place if necessary.