Average if criteria not blank

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.