Excel AVERAGEIF function to average cells with condition

Excel AVERAGEIF - things to remember!

To efficiently use the AVERAGEIF function in your worksheets, take notice of these key points:

• When calculating an average, empty cells, text values, and logical values TRUE and FALSE are ignored.
• Zero values are included in the average.
• If a criteria cell is empty, it is treated as a zero value (0).
• If average_range contains only blank cells or text values, a #DIV/0! error occurs.
• If no cell in range meets criteria, a #DIV/0! error is returned.
• The Average_range argument does not necessarily have to be of the same size as range. However, the actual cells to be averaged are determined by the size of the range argument. In other words, the upper left cell in average_range becomes the starting point, and as many columns and rows are averaged as contained in the range argument.

AVERAGEIF text criteria

To find an average of numeric values in a given column if another column contains certain text, you build an AVERAGEIF formula with text criteria. When a text value is included directly in the formula, it should be enclosed in double quotes ("").

For example, to average the numbers in column B if column A contains "Apple", the formula is:

=AVERAGEIF(A3:A15, "apple", B3:B15)

Alternatively, you can input the target text in some cell, say F3, and use that cell reference for criteria. In this case, double quotes are not needed.

=AVERAGEIF(A3:A15, F3, B3:B15)

The advantage of this approach is that it lets you average sales for any other item by simply changing the text criteria in F3, without having to make any adjustments to the formula.

AVERAGEIF logical criteria for numeric values

To test various numeric values in your criteria, use them together with "greater than" (>), "less than" (<), equal to (=), not equal to (<>), and other logical operators.

When including a logical operator with a number, remember to enclose the whole construction in double quotes. For example, to average the numbers that are less than or equal to 120, the formula would be:

=AVERAGEIF(B3:B15, "<=120")

Pay attention that the operator and number are both enclosed in quotes.

When using the "is equal to" criteria, the equality sign (=) can be omitted.

For instance, to average the sales delivered on 9-Sep-2022, the formula goes as follows:

=AVERAGEIF(C3:C15, "9/9/2022", B3:B15)

Using AVERAGEIF with dates

Similar to numbers, you can use dates as criteria for the AVERAGEIF function. Date criteria can be constructed in a few different ways.

Let's take a look at how you can average sales delivered before a given date, say November 1, 2022.

The easiest way is to enclose the logical operator and date together in double quotes:

=AVERAGEIF(C3:C15, "<11/1/2022", B3:B15)

Or you can enclose the operator and the date in quotes separately and concatenate them using the & sign:

=AVERAGEIF(C3:C15, "<"&"11/1/2022", B3:B15)

To make sure the date is entered in the format that Excel understands, you can use the DATE function concatenated with the logical operator:

=AVERAGEIF(C3:C15, "<"&DATE(2022, 11, 1), B3:B15)

To average sales delivered by today's date, use the TODAY function in the criteria:

=AVERAGEIF(C3:C15, "<"&TODAY(), B3:B15)

The screenshot below shows the results:

AVERAGEIF greater than 0

By design, the Excel AVERAGE function skips blank cells but includes 0 values in calculations. To only average values greater than zero, use ">0" for criteria.

For example, to calculate an average of the numbers in B3:B15 that are greater than zero, the formula in E4 is:

=AVERAGEIF(B3:B15, ">0")

Please notice how the result differs from a normal average in E3:

Average if not 0

The above solution works nicely for a set of positive numbers. If you have both positive and negative values, then you can average all numbers excluding zeros using "<>0" for criteria.

For instance, to average all the values in B3:B15 except zeros, use this formula:

=AVERAGEIF(B3:B15, "<>0")

Excel average if not zero or blank

As the AVERAGEIF function skips empty cells by design, you can simply use the "not zero" criteria ("<>0"). As a result, both zero values and blank cells will be ignored. To make sure of this, in our sample data set, we replaced a couple of zero values with blanks, and got absolutely the same result as in the previous example:

=AVERAGEIF(B3:B15, "<>0")

Average if another cell is blank

To average cells in a given column if a cell in another column in the same row is blank, use "=" for criteria. This will include empty cells that contain absolutely nothing - no space, no zero-length string, no non-printing characters, etc.

To average values corresponding to visually blank cells including those that contain empty strings ("") returned by other functions, use "" for criteria.

For testing purposes, we will use both criteria to average the numbers in B3:B15 that have no delivery date in C3:C15 (i.e. if a cell in column C is blank).

=AVERAGEIF(C3:C15, "=", B3:B15)

=AVERAGEIF(C3:C15, "", B3:B15)

Because one of the visually blank cells (C12) is not really empty - there is a zero-length string in it - the formulas deliver different results:

Average if another cell is not blank

To average a range of cells if a cell in another range is not blank, utilize "<>" for criteria.

For instance, the following AVERAGEIF formula calculates an average of cells B3 through B15 if a cell in column C in the same row is not blank:

=AVERAGEIF(C3:C15, "<>", B3:B15)

AVERAGEIF wildcard (partial match)

To average cells based on partial match, use wildcard characters in the criteria of your AVERAGEIF formula:

• A question mark (?) to match any single character.
• An asterisk (*) to match any sequence of characters.

Suppose you have 3 different sorts of banana, and you want to find their average. The following formula will make it happen:

=AVERAGEIF(A3:A15, "*banana", B3:B15)

If needed, a wildcard character can be used together with a cell reference. Assuming the target item is in cell В4, the formula takes this shape:

=AVERAGEIF(A3:A15, "*"&D4, B3:B15)

If your keyword may appear anywhere in a cell (in the beginning, in the middle, or in the end), place an asterisk on both sides:

=AVERAGEIF(A3:A15, "*banana*", B3:B15)

To find the average of all items excluding any banana, use this formula:

=AVERAGEIF(A3:A15, "<>*banana*", B3:B15)

How to calculate average in Excel excluding certain cells

To exclude certain cells from the average, use the "not equal to" (<>) logical operator.

For example, to average the sales numbers for all the items except "apple", use this formula:

=AVERAGEIF(A3:A15, "<>apple", B3:B15)

If the excluded item is in a predefined cell (D4), the formula takes this form:

=AVERAGEIF(A3:A15, "<>"&D4, B3:B15)

To find the average of all items excluding any "banana", use the "not equal to" together with a wildcard:

=AVERAGEIF(A3:A15, "<>*banana", B3:B15)

In case the excluded wildcard item is in a separate cell (D9), then concatenate the logical operator, wildcard character and cell reference using an ampersand:

=AVERAGEIF(A3:A15,"<>"&"*"&D9, B3:B15)