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")`

.

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