Excel: Find average based on multiple criteria

Last week, I showed you how to find an average based on a single criterion. That works well enough, but what if you want to find an average based on multiple criteria? In that case, the AVERAGEIF-function will fall short. In its place, you must use the AVERAGEIFS-function. This time, we want to find the averages for both ALPHA and BRAVO.

Here’s how you build your function:

  • Range to calculate average
  • First criterion range
  • First criterion for evaluation
  • Second criterion range
  • Second criterion for evaluation

My example here uses column B as the range for average calculation, column A as the first criterion range, a unique value as the first criterion to evaluate, column C as the second criterion range, and ALPHA as the first criterion to evaluate. The actual function looks like this =AVERAGEIFS(B:B,A:A,D2,C:C,"ALPHA"), and the result looks as follows:

The function is not case sensitive. In other words, if I were to replace ALPHA with AlPhA, alpha, or any other combination of upper and/or lower case, it would still return the same result.

If there are no results, however, Excel returns #DIV/0. Luckily, you can use the IFERROR-function to ensure that the function fails gracefully. In that case, the function would look something like this: =IFERROR(AVERAGEIFS(B:B,A:A,D2,C:C,"ALPHA"),"N/A").






By posting a comment, you consent to our collecting the information you enter. See privacy policy for more information.

This site uses Akismet to reduce spam. Learn how your comment data is processed.