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: