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:

• =AVERAGEIF
• 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").