How to Use the AVERAGEIF and AVERAGEIFS Functions in Excel

Excel’s AVERAGEIF and AVERAGEIF functions let you find the average of a set of data. However, where they differ from the more straightforward AVERAGE function is in their ability to include or discount certain values within the calculation.

How to Use AVERAGEIF in Excel

Excel’s AVERAGEIF function allows you to calculate the mean of a dataset that has been filtered based on a single condition you set.

The AVERAGEIF Syntax

The AVERAGEIF function has three arguments:

=AVERAGEIF(x,y,z)

where

• x (required) is the range of cells to test against the criteria,
• y (required) is the criterion (the test for argument x), and
• z (optional) is the range of cells to average if the test in argument y is met. If you leave z out, Excel will average the values identified in argument x.

AVERAGEIF in Action

Let’s jump straight in and see how the AVERAGEIF function works in a real-world example.

Let’s suppose you have this Excel table containing 12 people’s names, ages, and salaries, and you’ve been asked to calculate the average salaries of people aged over 40.

In this case, thinking back to the syntax above, column B contains the values you want to test (argument x), more than 40 is the criterion (argument y) for the values in column B, and column C contains the values you want to average (argument z).

So, in a separate cell, you need to type:

=AVERAGEIF(Table1[Age],">40",Table1[Salary])

and press Enter.

Let’s take a moment to break this formula down.

The first thing you’ll notice is the use of structured references for arguments x and z. In other words, rather than using direct cell references (such as B2:B13 for argument x and C2:C13 for argument z), the formula references the column headers. This is because the data is contained within a formatted Excel table, and the program defaults to using the table headers in formulas. This means that if you add extra rows to the bottom of your data, the formula will automatically include those new values.

Second, argument y is contained within double quotations. Any time you use logical operators in Excel formulas—like equal to, greater than, or less than—they must be placed within double quotations.

Things to Note When Using AVERAGEIF

Before you go ahead and use AVERAGEIF in your own spreadsheet, there are some important points you should be aware of.

First, argument y (the test) is very flexible. While the example above uses “>40” (a logical operator) to test the range in argument x, there are various other types of criteria you could use instead:

Second, the AVERAGEIF function doesn’t consider empty cells. For example, if someone’s salary value was blank, it would be ignored in the average calculation. However, if someone’s salary was $0, this would be included in the average calculation.

Finally, if none of the specified cells meets the test, Excel returns the #DIV/0! error to tell you that it can’t calculate the average.

How to Use AVERAGEIFS in Excel

Where AVERAGEIF tests one condition before calculating the average of all values that meet the test, AVERAGEIFS allows you to narrow your results even further by using several criteria.

The AVERAGEIFS Syntax

It’s important to note that the order and number of arguments in the AVERAGEIFS function differs significantly from the AVERAGEIF function:

=AVERAGEIFS(x,y¹,y²,z¹,z²,...)

where

• x (required) is the range of cells containing the values to be averaged,
• y¹ (required) is the range of cells containing the first values to be tested,
• y² (required) is the test for y¹,
• z¹ (optional) is the range of cells containing the second values to be tested, and
• z² (required if z¹ is included) is the test for z¹.

In other words, the syntax above represents the AVERAGEIFS function being used to create two tests (y and z), though you can include up to 127 tests overall.

AVERAGEIFS in Action

If the AVERAGEIFS syntax confuses you, things will become much clearer when you see how the function works in an example.

This Excel table contains people’s names, ages, genders, and salaries, and your aim is to work out the average salary of all males over the age of 35.

Since there are two criteria (age and gender), you need to use the AVERAGEIFS function:

=AVERAGEIFS(Table2[Salary],Table2[Age],">35",Table2[Gender],"M")

where

• Table2[Salary] is the range of cells containing the values to be averaged (argument x),
• Table2[Age] is the range of cells containing the first set of values to be tested (argument y¹),
• “>35” is the test for the first range (argument y²),
• Table2[Gender] is the range of cells containing the second set of values to be tested (argument z¹), and
• “M” is the test for the second range (argument z²).

Things to Note When Using AVERAGEIFS

Here are some things to remember when using the AVERAGEIFS function:

• The tests can be logical arguments, values, text, cell references, or a combination of these.
• Logical arguments and text must always be enclosed in double quotations.
• Only cells that meet all conditions will be included in the average calculation.
• All ranges specified in an AVERAGEIFS formula must be the same size.
• The AVERAGEIFS function discounts empty cells but includes cells with a value of zero.
• The #DIV/0! error will be returned if no cells meet all the criteria.

AVERAGEIF and AVERAGEIFS aren’t the only functions in Excel that calculate the average of a dataset. For example, the AVERAGE function produces a straightforward arithmetic mean of a set of data, and the AVERAGEA function returns the mean of a set of numbers, text, and logical arguments.