Average if not blank

In this example, the goal is to average the Prices in C5:C16 when the Group in D5:D16 is not blank (i.e. not empty). The traditional way to solve this problem is to use the AVERAGEIFS function. However, you can also use the FILTER function with the AVERAGE function, as explained below. Because FILTER can work with ranges and arrays, it is a more flexible solution.

Background study

• How to use the AVERAGEIFS function
• FILTER function basic example

AVERAGEIFS Function

The AVERAGEIFS function calculates the average of cells in a range that meet one or more conditions, referred to as criteria. To apply criteria, the AVERAGEIFS function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. The generic syntax for AVERAGEIFS looks like this:

=AVERAGEIFS(avg_range,range1,criteria1)

In this case, we need to test for only one condition, which is that cells in D5:D16 are not blank. We start off with the avg_range, which contains the prices in C5:C16:

=AVERAGEIFS(price,

Next, we add the range that we need to test, the group values in D5:D16:

=AVERAGEIFS(price,group,

Finally, we add the criteria, which is the not equal to operator (<>), which must be enclosed in double quotes (""):

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

The result is 354,575, the average of Prices in C5:C16 when corresponding cells in D5:D16 are not blank (i.e. have group values). The main challenge with AVERAGEIFS is the quirky syntax. For criteria, we simply use the "not equal to" operator, "<>". We don't provide a value, and it's implied that this means "not equal to nothing", i.e. "not blank". To read more about how to use the AVERAGEIFS function with logical operators and wildcards, see this page.

Excluding formulas

The formula above will treat empty strings returned by formulas as not empty. If you have empty strings returned by formulas in the criteria range, you can adjust the criteria like this:

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

This version of the formula will treat empty strings returned by formulas as blank.

FILTER function

In the current version of Excel, another approach is to use the FILTER function with the AVERAGE function in a formula like this:

=AVERAGE(FILTER(price,group<>""))

In this formula, we are literally removing values we don't want to average. The FILTER function is configured to return only values in C5:C16 when cells in D5:D16 are not empty. FILTER returns the 8 values in the data that meet this condition directly to the AVERAGE function:

=AVERAGE({355000;209900;448000;129900;189000;385000;679900;439900})

The AVERAGE function calculates an average and returns a final result of 354,575. FILTER is a more flexible function that can apply criteria in ways that AVERAGEIFS can't. For more on the FILTER function, see this page.