Average if criteria not blank
=AVERAGEIFS(range1,range2,"<>")
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.
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.
