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.

A knowledge of the basic AVERAGEIF and SUMIFS functions is required for this tutorial. Take a look at our Excel AVERAGEIF tutorial before proceeding.


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:

Excel's AVERAGEIFS function in action
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.

Save an hour of work a day with these 5 advanced Excel tricks

Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on getting Excel to do your work for you.

  • How to create beautiful table formatting instantly...
  • Why to rethink the way you do VLOOKUPs...
  • Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...

By submitting this information, you agree to Deskbright's privacy policy and terms of service.