AVERAGEIF with multiple criteria: AVERAGEIFS

Finally, we'll take a look at Excel's AVERAGEIFS function. Like SUMIFS, AVERAGEIFS will allow us to average rows or columns in a table that satisfy multiple specified conditions.

Using AVERAGEIFS

Here's the syntax for the AVERAGEIFS function:

=AVERAGEIFS(average_range, criteria_range_1, criteria_1, criteria_range_2, criteria_2...)

The function is very similar to SUMIFS, except that it AVERAGEs values rather than SUMming them. Note that the AVERAGEIFS function accepts an unlimited number of arguments, as long as they come in pairs (because every criteria_range needs a criteria to check against).

Let's apply the above formula to our SnackWorld example, using it to find the average Candy sales to CandyLand:

=AVERAGEIFS(E3:E9,C3:C9,H4,D3:D9,H5)
Output: $3,500,000

First, the above formula looks at our initial average_range and sees cells E3:E9, which will be included in our average if they meet certain criteria. Then, it examines our initial criteria_range, C3:C9, and compares it against our initial criteria, Candy (referenced in cell H4), to determine how many rows satisfy our query. Then, as a next step, it looks at our secondary criteria_range, D3:D9, and references it against our secondary criteria, CandyLand (referenced in cell H5). The resulting output is the number $3,500,000, because two rows in our table list the phrase Candy in the Category column and the phrase CandyLand in the Customer column.

Comments