## How to Use the Excel AVERAGEIF Function

In this tutorial, you'll learn how to use the **Excel AVERAGEIF function**.

**AVERAGEIF** is a function to calculate the average of a group of numbers in a range of cells that meet single criteria.

**AVERAGEIF **Syntax

The syntax of the **AVERAGEIF**() function is as follows:

=

AVERAGEIF(range,criteria, [average_range])

### Arguments

: A range of cells*range*: The criteria that define which cells to average*criteria*: Optional. A range of cells to average. When omitted,*average_range*is a range of cells to average.*range*

### Remarks

can numbers, ranges, names, arrays, or cell references.*range*can be a number, cell reference, expression, or text.*criteria***AVERAGEIF**() ignores the empty cells in*average_range**.*- Cells that contain TRUE or FALSE are ignored.
**AVERAGEIF**() returns the error value (#DIV/0!) if there is no cells in themeet the criteria.*range*- The size and shape of
and*average_range*don't have to be the same.*range* **AVERAGEIF**()*****) and (**?**) in.*criteria*

### AVERAGEIF Function Examples

Demo data:

A | B | C | D | E | F | G | |

1 | Date | Code | Item Name | Quantity | Price ($) | Amount ($) | Employee |

2 | 6/10/2019 | Item010 | Desktop PC 10 | 2 | 250 | 500 | James |

3 | 6/10/2019 | Item020 | Desktop PC 20 | 3 | 1000 | 3000 | James |

4 | 6/10/2019 | Item010 | Desktop PC 10 | 1 | 250 | 240 | James |

5 | 6/10/2019 | Item030 | Desktop PC 30 | 3 | 300 | 900 | Roland |

6 | 6/11/2019 | Item020 | Desktop PC 20 | 5 | 1000 | 5000 | Roland |

7 | 6/12/2019 | Item010 | Desktop PC 20 | 1 | 250 | 250 | James |

8 | 6/12/2019 | Item030 | Desktop PC 30 | 4 | 300 | 1200 | Roland |

#### Example 1

The following example calculates the average of cells that the amount is greater than 1000 in the range F2: F8:

=

AVERAGEIF(F2:F8,">1000")

Here, the *average_range* argument is omitted. the *range* argument is a range of cells to average.

The above formula returns 3066.667.

#### Example 2

The following example calculates the average of all amounts by the employee named *Roland*:

=

AVERAGEIF(G2:G8,"Roland",F2:F8)

The range G2: G8 is a criteria range and the range F2: F8 is a range of cells to average.

The above formula returns 2366.667.

#### Example 3

You can perform a partial matching in criteria, as the following example shows:

=

AVERAGEIF(G2:G8,"J*",F2:F8)

The above example calculates the average of all amounts of the employee that name begins with a letter *J* and ends with any *text* or *string*.

The above formula returns 997.5, which is the average of all amounts by the employee *James*.

#### Example 4

Here is another example of partial matching in criteria:

=

AVERAGEIF(G2:G8,"Rola??",F2:F8)

In the above formula, the criteria **"Rola??" **matches any employee that name begins with *Rola* and ends with *any two characters*.

As you can see in the above demo data, the employee named *Roland* matches this case.

The above formula returns 2366.667.

In this tutorial, you've learned how to use the **Excel AVERAGEIF function**.

**AVERAGEIF** is a function to calculate the average of a group of numbers in a range that meets a criteria.

There are other **AVERAGE** functions as described below.

- If you want to calculate the average of a group of numbers, use
**AVERAGE**(). - If you want to calculate the average of only the values that meet the multiple criteria, use
**AVERAGEIFS**(). - If you want to include logical values and text representations of numbers in a reference as part of the calculation, use
**AVERAGEA**().