Summary

To calculate an average, excluding blank values in a criteria range, you can use the AVERAGEIFS function and a simple logical expression. In the example shown, the formula in F6 is:

=AVERAGEIFS(price,group,"<>")

where "price" (C5:C15) and "group" (D5:D15) are named ranges.

Generic formula

=AVERAGEIFS(range1,range2,"<>")

Explanation 

Excel supports a number of logical operators that can be used in various ways to construct criteria. In this example, we are using the not equals to operator (<>) to exclude values with no group in D5:D15. The range and criteria are supplied as two arguments:

D5:D15,"<>"

Which is equivalent to the expression:

D5:D15<>""

which means not equal to an empty string, e.g. not empty.

Excluding formulas

The formula above will not exclude empty strings returned by formulas (=""). If you have empty strings returned by formulas in the criteria range, you can adjust the criteria like this:

=AVERAGEIFS(C5:C15,D5:D15,">""")

This will correctly exclude empty string returned by formulas.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.