Excel 2019 Bible

Part II: Working with Formulas and Functions

Figure 13.15 once again shows the medalists’ results from the 1972 Winter Olympics. We

want to determine the average result but only for those skiers from Switzerland. The coun-

try code is entered in cell I3 so that it can be easily changed to a different country.

=AVERAGEIF(D3:D20,I3,E3:E20)

FIGURE 13.15

Averaging results based on a country

Excel provides the AVERAGEIF function to accomplish just what we want. Like its cousin,

the SUMIF function, AVERAGEIF has a criteria_range and a criteria argument. The final

argument is the range to average. In this example, each cell in E3:E20 is either included in

or excluded from the average depending on whether the corresponding cell in D3:D20 meets

the criteria.

If no rows meet the criteria in AVERAGEIF, the function returns the #DIV/0! error.

Getting the average of all numbers that meet two

or more conditions

Microsoft introduced AVERAGEIFS along with SUMIFS and COUNTIFS to allow you to aver-

age a range of numbers based on more than one condition.

Continuing our analysis of skiing times, Figure 13.16 shows some results of the 1972 Winter

Olympics. In this case, we want to determine the average time based on more than one