Excel AVERAGEIF function to average cells with condition

The tutorial shows how to use the AVERAGEIF function in Excel to calculate an arithmetic mean with condition.

Microsoft Excel has a few different functions to calculate an arithmetic mean of numbers. When you are looking to average cells that meet a certain condition, AVERAGEIF is the function to use.

• Excel AVERAGEIF function
• AVERAGEIF based on another cell
• How to use AVERAGEIF in Excel - examples
  • Average if cell contains certain text
  • AVERAGEIF logical criteria
  • AVERAGEIF date criteria
  • Average if greater than 0
  • Average if not zero
  • Average if not zero or blank
  • Average if blank
  • Average if not blank
  • AVERAGEIF with wildcards
  • Average excluding certain cells
• AVERAGEIF with cell reference

AVERAGEIF function in Excel

The AVERAGEIF function is used to calculate an average of all cells in a given range that meet a certain condition.

AVERAGEIF(range, criteria, [average_range])

The function has a total of 3 arguments - the first 2 are required, the last one is optional:

• Range (required) - the range of cells to test against the criteria.
• Criteria (required)- the condition that determines which cells to average. It can be supplied in the form of a number, logical expression, text value, or cell reference, e.g. 5, ">5", "cat", or A2.
• Average_range (optional) - the cells you actually want to average. If omitted, then range will be averaged.

The AVERAGEIF function is available in Excel 365 - 2007.

Tip. To average cells with two or more criteria, use the AVERAGEIFS function.

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 formula based on another cell

With the Excel AVERAGEIF function, you can average a column of numbers based on:

• criteria applied to the same column
• criteria applied to another column

In case the condition applies to the same column that should be averaged, you define only the first two arguments: range and criteria. For example, to find an average of sales in B3:B15 that are greater than $120, the formula is:

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

To average based on another cell, you define all 3 arguments: range (cells to check against the condition), criteria (the condition) and average_range (cells to calculate).

For instance, to get an average of sales that were delivered after Oct-1, the formula is:

=AVERAGEIF(C3:C15, ">1/10/2022", B3:B15)

Where C3:C15 are the cells to check against the criteria and B3:B15 are the cells to average.

How to use AVERAGEIF function in Excel - examples

And now, let's see how you can use Excel AVERAGEIF in real-life worksheets to find an average of cells that meet your criteria.

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.

Tip. To round an average to a certain number of decimal places, make use of the Increase Decimal or Decrease Decimal command on the Home tab, in the Number group. This will change the display representation of the average but not the value itself. To round the actual value returned by the formula, use AVERAGEIF together with ROUND or other rounding functions. For more information, please see How to round average in Excel.

AVERAGEIF logical criteria for numeric values

To test various numeric values in your criteria, use them together with "greater than" (>), "less than" (), 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, "

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, "

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

=AVERAGEIF(C3:C15, "

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, "

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

=AVERAGEIF(C3:C15, "

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)

How to use AVERAGEIF with cell reference

Instead of typing the criteria directly in a formula, you can use a logical operator in combination with a cell reference to construct the criteria. This way, you will be able to test different conditions by changing a value in the criteria cell without editing your AVERAGEIF formula.

When the condition defaults to "is equal to", you simply use a cell reference for the criteria argument. The below formula calculates the average of all sales within the range B3:B15 relating to the item in cell F4.

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

When the criteria includes a logical operator, you build it in this way: enclose the logical operator in quotation marks and use an ampersand (&) to concatenate it with a cell reference.

For example, to find the average of sales in B3:B15 that are greater than the value in F9, use the following formula:

=AVERAGEIF(B3:B15, ">"&F9)

In a similar fashion, you can use a logical expression with another function in the criteria.

With dates in C3:C15, the below formula returns the average of sales that have been delivered up to the current date inclusive:

=AVERAGEIF(C3:C15, "

That's how you use the AVERAGEIF function in Excel to calculate an arithmetic mean with condition. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

Excel AVERAGEIF function - examples (.xlsx file)

The above is the detailed content of Excel AVERAGEIF function to average cells with condition. For more information, please follow other related articles on the PHP Chinese website!