Averageif and Averageifs are used to calculate the arithmetic average of cells that meet specified criteria(s).

The functions are available in MS Excel 2007 onward, and Mac Excel 2011 onward.

Averageif

Syntax

=AVERAGEIF (range, criteria, [average_range])

  • Range Required. These are the ranges that contain the criteria, or if average_range argument is omitted, the actual range that contains the numbers to average.
  • Criteria Required. The criteria that dictates what cells to average. It can use a logical expression such as “>5”, “<=0”, or plain expressions such as “10”, “Product”, or expressions containing wildcard characters such as “???” or “*product”. The criteria can refer to a range that contains the expression.
  • Average_range Optional. Cells to apply average on. The first range argument is used if this is omitted.

AVERAGEIF() ignores text values. If you need to evaluate text values as 0 in calculation, use AVERAGEA() function with IF() function instead.

Averageifs

Syntax

=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

The AVERAGEIFS function syntax has the following arguments:

  • Average_range Required. Cells to apply average on.
  • Criteria_range1, criteria_range2, … Only criteria_range1 is required. Maximum of 127 criteria ranges allowed. t can use a logical expression such as “>5”, “<=0”, or plain expressions such as “10”, “Product”, or expressions containing wildcard characters such as “???” or “*product”. The criteria can refer to a range that contains the expression.

Notice that unlike AVERAGEIF() function, average_range is the first argument here.

Also, all the specified criteria must be true for it to not show #DIV/0! error. That is, you are effectively specifiying an “AND” condition where criteria1, criteria2 etc must all be true for their respective ranges.

Usage

Averageif Not Zero

The formula to average all the non-zero cells in this example is

=AVERAGEIF($A$1:$A$9, “<>0”)

You can choose not to use dollar signs ($) to fix the cell reference if the formula is not to be applied elsewhere.

Applying Average if Cells Are Not Equal To a Text

averageif example data

If you want to only average those cells where criteria is where cells are not equal to certain text, then an example formula is

=AVERAGEIF($A$2:$A$17, “<>Central”, $D$2:$D$17)

This will average those regions that are not Central.

Applying Logical Operators on a Cell Range

When you use logical operators (<, >, <>, =, <= etc) together with a cell that contains the value to compare against, you need to enclose the logical operators separately, then use ampersand (&) symbol to connect the cell reference.

For example, to average a range A2:A8 if they are less than a value in cell B2, you would write

=Averageif(A2:A8, “<” & B2)

Averageif Multiple Criteria

Averageif function only allows one criteria. If you need to apply several criteria, you can use AVERAGEIFS() function.

Specifically, if you need to specify multiple OR conditions, you may use wildcards to do so. (A word of caution, you need to be very familiar with your dataset so that you do not accidentally include items you are not after). The screenshot contains the data for the example below.

averageif data

An example involving wildcards:

Suppose you want the Average Floor Area (m2) for East and West Region.

An alternative solution is provided in Averageif Two Criteria In Same Column section.

averageif wildcards fuzzy search

The formula is:

=AVERAGEIF(A2:A26, “*st”, B2:B26)

where the star sign (*) represents one or more unknown characters.

This asks the spreadsheet program to do average on column B if column A contains words that end in “st”.

Another example, suppose you want the Average Floor Area (m2) for North Region.

The formula could be:

=AVERAGEIF(A2:A26, “N??th”, B2:B26)

Here, each question mark symbol (?) denotes one unknown character.

Essentially this criteria looks for any word beginning with N followed by any two characters then end in “th”. The first “N” precludes “South”, while the rest of the string also specifies the length of the word.

Averageif Google Sheets

The methods explained in this post applies to both Excel and Google Sheets.

This is true also for AVERAGEIFS, SUMIF, SUMIFS, COUNTIF and COUNTIFS.

Averageif Multiple Ranges

If you have multiple ranges to take into account, the best method is to use AVERAGEIFS() function instead.

Suppose you want to find properties in Central where floor area is between 30 and 50 sqm inclusive.

averageifs for multiple ranges

The formula would be

=AVERAGEIFS(B2:B17,A2:A17, “Central”, B2:B17, “>=30”, B2:B17, “<=50”)

This requires Excel or Google Sheets to find rows in Region column that meets region criteria, AND where floor area column is between a desired interval.

Vlookup Averageif

You can combine VLOOKUP() function with AVERAGEIF() to determine what category the averaged amount falls into.

For example, suppose we put a description label for different floor areas:

Floor Area (m2)Label
0Lacking
40Small
50Average
60Good
70Comfortable
vlookup averageif

Then the formula for the description applicable to an average dwelling in Central region would be

=VLOOKUP( AVERAGEIF(A2:A17, “Central”, B2:B17), D3:E7, 2,1 )

vlookup averageif cell reference

You can also put the word “Central” in another cell, say D9, and rewrite the formula as:

=VLOOKUP( AVERAGEIF(A2:A17, D9,B2:B17), D3:E7,2,1 )

Averageif Two Criteria In Same Column

This is effectively asking for an OR condition where as long as one of criteria1, criteria 2…is true, do the average.

Suppose the aim is to find the “Average Price / m^2 for East and West Region”

It is easy to make the mistake of writing a formula as such:

=AVERAGEIFS(D2:D17, A2:A17, “East”, A2:A17, “West”)

which gives #DIV/0! error. The reason being that you cannot ask a range to be both “East” and “West” at the same time.

What you are after is those regions that are either “East” or “West”.

The other formula below is also wrong:

=AVERAGEIFS(D2:D17, A2:A17, “East”)+AVERAGEIFS(D2:D17, A2:A17, “West”)

This is because the sum of averages is not the same as the average of total.

In this case you can either use “not equal to Central”, such as

=AVERAGEIF(A$2:A$17, “<>Central”, D2:D17)

or the following formula:

=( SUMIFS(D2:D17,A2:A17, “East”)+SUMIFS(D2:D17, A2:A17, “West”) ) / (COUNTIF(A2:A17, “East”)+COUNTIF(A2:A17, “West”))

This sums up all the values if they meet the criteria, then do an average by dividing a count of items that meet those criteria.

#DIV/0! Error

If you get DIV/0 error, check if any of the following is the case:

  • The specified range does not contain your criteria
  • There are no numbers in the average_range
  • What looks like numbers in the average_range are formatted as text that cannot be converted to numbers

Related Posts

Pin It on Pinterest

Share This