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.

How this formula works

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.